cancel
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

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.  

 

 

Status: New
Comments
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).

 

Udklip.PNG

Super Contributor

@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] )
)

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

 

@mifo123

 

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

 

Best Regards,
Herbert

Member

 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