Use the Power BI quick calcs with your own date table

Just a quick one today on how you can use the Power BI quick calcs with you own date table.

I have a very simple table with a date column in it:

Now I want to show the YTD over time (so crossing multiple years). It is pretty straightforward to do this:

But I want a line chart that show a line at the day level (so a full date not day of year), unfortunately that isn’t possible today as the hierarchy only support year, quarter of year, month of year and day of year. But when I change to my real date column I get:

Calculation error in measure ‘Activities'[Number of Activities YTD]: Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy.

So that means I can only use the build in date hierarchy but I need more. So let’s change this calculation and add my own date.

First thing I do is creating a new separate date table, I go to modelling and click new table:

Here I enter the following DAX function

Date = CALENDARAUTO()

This generates a date table based on the values in the model.
One another annoying side effect of the desktop “date” helper functions is that it throws off the CALENDARAUTO function, you will see the date start at 1899 because that is what the hidden date tables have as value. To control this you can change the expression to your date range:

Date = CALENDAR("1/1/2016","31/12/2017")

Next I create a relationship between the two tables:

Now I replace the date column from the base table with the column from the date table in my visual. This again throws this message:

Calculation error in measure ‘Activities'[Number of Activities YTD]: Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy.

let’s go ahead and change the expression from:

Number of Activities YTD = 
IF(
 ISFILTERED('Activities'[start_date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
 TOTALYTD('Activities'[Number of Activities], 'Activities'[start_date].[Date])
)

to:

Number of Activities YTD = 
TOTALYTD(
 'Activities'[Number of Activities], 'Date'[Date]
)

I made two changes here, I got rid of the IF statement that was testing if we actually use the ‘Activities'[start_date] column in the visual (which we don’t want) and changed the YTD to use the ‘Date'[Date] column instead of the ‘Activities'[start_date] column.

Now I get the result I want:

This trick works with all quick calcs that are based on dates. The Power BI team is working on supporting this out of the box as well.