Forum, Tips & Tricks

Learn tips and tricks and engage in discussions with our blog authors here. It’s all for your benefit and learning. 

Please or Register to create posts and topics.

Days in a month #DAX #DATEDIFF

For any reporting purposes with date slicers, the data needs to be structured in a way that each row represents one day.

Transforming dataPicture 1: example of data transformation

This allows Power BI to properly adjust the values according to the date selection since the date is transformed into one column (also helps with relationships) with respective value.

IF the available data only consists of monthly values but at the same time, the report needs to have a per-day structure (see above), it can be useful to create an additional column with respective numbers of days in a month. This way we can create an approximation by dividing the monthly values by the number of days in said month.

Days in a monthPicture 2: Each day transformed into YYYY-MM ("days_month") with respective numbers of days in the month ("days_in_month")

In this example we use the function DATEDIFF:

Days_in_month = DATEDIFF
                                       (DATE(LEFT(Report[Days_month];4);RIGHT(Report[Days_month];2);1);
                                        ENDOFMONTH((Report[Days]));
                                        DAY)
                                 +1

We use functions DATE, LEFT, and RIGHT to extract and create a date for the DATEDIFF function. Basically we create the first day of the month e.g. by having DATE(2016; 02; 1). We calculate the difference by using ENDOFMONTH as a reference point, e.g. the difference between 01/02/2016 and 29/02/2016.

It is important to note that we need to add +1 because there are only 28 days between 29/02 and 01/02 but there are 29 days in that month.

Jan Havelka and Deleted user have reacted to this post.
Jan HavelkaDeleted user

Learning by doing: simple solution is to use a Power Query that allows you to transform a date to the end of the month.

Picture 1: Power Query - transforming dates

Then the first two characters can be extracted to create the column with a number of days.

Outstanding submit, travelled in advance and also book marked your internet site. My partner and i can’t hold out to learn a lot more coming from an individual. N95 mask

This blog was extremely helpful. I really appreciate your kindness in sharing this with me and everyone else! THC gummies

Find the right Luxury hotel for your honeymoon Rawnaq Tourism recommends the Best Maldives Hotels ​depending on your individual needs

Find the right career on unified career fast and easy in your city. List jobs here for free and find the right candidate. Unified Career is here to help you. career choices

TR Financial are experienced mortgage advisor based in Edgware, covering Wembley, Stanmore, and all over North West London, UK. Our Insurance Agency and Mortgage advisor providing best possibe solution. Our mission is to provide you the best mortgage and insurance deals possible. Mortgage Advisor Edgware