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:

image

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

image

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:

image

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:

image

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:

image

 

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.

5 Replies to “Use the Power BI quick calcs with your own date table

  1. I have tried this: Net MTD = TotalMTD(SUM(‘Sales Report'{NET]0, ‘Date'{Date}) and I cannot get a MTD sales measure to work. I hit enter and nothing happens. Prior to that I removed the If statement.

    1. It looks like you got some of your brackets wrong. It should look like this
      Net MTD = TotalMTD(SUM(‘Sales Report'[NET]), ‘Date'[Date])
      Make sure your date table contains all the dates needed and has an active relationship.

      1. Sorry those were typos. I have it the same as you typed it. I am not familiar with DAX, and learning as I go. I am sure there is a simple answer. Will keep working on it.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.