Showing results for 
Search instead for 
Did you mean: 

TOTALYTD measure not working if Calendar is connected via DateKey


I've come across an issue where TOTALYTD doesn't work when a Calendar is connected to the Fact Table via Date Key rather than a Date.


I've replicated the issue in an Excel file but the same issue applies in PBI!Auy_rL3prGGUiT8m1WzkaXvRjxNN



Interestingly if you create an inactive relationship between the actual Date columns in the Calendar and the Fact Table then the YTD issue resolves itself.  



Status: New
Frequent Visitor

I also have a similiar issue for the rolling average quick measure.  Despite having a Calendar table with a DateKey the measure does not work after the October update. It however works with other quick measures (incl. YTD).



Super Contributor



According to the official document about TOTALYTD Function (DAX), the date parameter should be a column that contains dates. But in your scenario, it is a column with unnique whole numbers which represent different dates.


To get the total YTD in your scenario, we can create a measure using following DAX formula. Before that, we need to create a Year column in the fact table.


SalesYTD_2 = 
    SUM ( 'Fact'[Sales] ),
    FILTER ( ALL ( 'Fact' ), 'Fact'[Date Key] <= MAX ( 'Fact'[Date Key] ) ),
    VALUES ( 'Fact'[Year] )

TOTALYTD measure not working if Calendar is connected via DateKey_1.jpg




Have you tried to use the date column in the fact table?


Best Regards,


 Thanks @v-haibl-msft


Yes my measure is created using the Date field and I have created many in the past, but this is the first time I've ever used a DateKey for the "relationship" between the tables.


It does seem odd that the same measure then works as soon as I create an inactive relationship between the 2 date fields in the 2 tables.


TOTAL YTD img1.pngTOTAL YTD img2.png