Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
mifo123
Advocate I

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

v-haibl-msft
Employee

@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

wynhopkins
MVP

 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

Anonymous
Not applicable

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
MVP

Hi @Anonymous 

 

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