Forum, Tips & Tricks

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

You need to log in 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.

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.

canadian pharmacies shipping to usa
http://canadianpharmaciesoffer.com/
[url=http://canadianpharmaciesoffer.com/]best online international pharmacies[/url]

prescription drugs online without
http://canadianpharmaciesoffer.com/
[url=http://canadianpharmaciesoffer.com/]online pharmacy canada[/url]