TOTALYTD measure not working if Calendar is connected via DateKey

[wynhopkins]

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

https://1drv.ms/x/s!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.  

[mifo123]

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).

[v-haibl-msft]

@wynhopkins

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 = 
CALCULATE (
    SUM ( 'Fact'[Sales] ),
    FILTER ( ALL ( 'Fact' ), 'Fact'[Date Key] <= MAX ( 'Fact'[Date Key] ) ),
    VALUES ( 'Fact'[Year] )
)

@mifo123

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

Best Regards,
Herbert

[wynhopkins]

 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.

[Anonymous]

Hi there,

probably ALL(whole date table) should be added as a filter argument of TOTALYTD function:

TOTALYTD(SUM([Sales Value]), 'Calendar'[Date], ALL('Calendar')).

Without this filter clearance it seems that setting any filter context (like Month, Year) filters both fact table and date table. That leads to incorrect YTD calculation. 

[wynhopkins]

Hi @Anonymous 

the date table needs to be "marked as a date table " for time intelligence functions to work with a date key