Forum Discussion

mp390988's avatar
mp390988
Post Partisan
1 year ago
Solved

DATESYTD function

Hello,   I have a measure defined as: RevenueYTD = CALCULATE(     [Revenue],     DATESYTD('CalendarTbl'[Date]) ) When I drop this measure into my visual, it does not show any values, as...
Need Help
  • mp390988's avatar
    mp390988
    1 year ago

    Hi FBergamaschi ,

    I realised that I had more than one table marked as date table. For example, I had both the Calendar and CalendarTbl marked as Date tables. As soon as I turned off Calendar as being Date table it worked!

    Thank You

wardy912's avatar
wardy912
Super User
1 year ago

Hi mp390988 

 

Why 'Calendar' works:
'Calendar' is a DirectQuery table from another semantic model.
It is already part of the model's relationships, likely connected to your fact table (where [Revenue] is calculated).
DATESYTD('Calendar'[Date]) works because Power BI can trace the relationship and apply the time intelligence function correctly.


Why 'CalendarTbl' does not work:
'CalendarTbl' is a calculated table created using SELECTCOLUMNS from 'Calendar'.
Calculated tables do not automatically inherit relationships from their source tables.
Unless you manually create a relationship between 'CalendarTbl' and your fact table, Power BI cannot resolve the filter context for [Revenue] through 'CalendarTbl'.

DATESYTD() needs a properly related date column to work in a time intelligence context.
If 'CalendarTbl' is not related to your fact table, the filter context created by DATESYTD('CalendarTbl'[Date]) has no effect on [Revenue].

 

You have a couple of options, in my opinion the first is the best option.

 

  1. Use the original 'Calendar' table in your measure.
  2. Create a relationship between 'CalendarTbl' and your fact table on the [Date] column.

 

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!