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

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
1 year ago

Hi mp390988,

Thank you for the detailed explanation and screenshots very helpful. Also, thanks to FBergamaschi, wardy912, for those inputs on this thread.

From your description, it looks like the issue is due to how the DATESYTD() function behaves in combination with your 'CalendarTbl' table.

The root cause is that 'CalendarTbl', which is created using SELECTCOLUMNS() from 'Calendar', is not recognized as a valid date table by Power BI’s time intelligence engine. This means that functions like DATESYTD() won’t work properly unless the [Date] column is from a table marked as a Date Table.

Your 'Calendar' table, coming from another semantic model, is likely marked as a proper Date Table so DATESYTD() works as expected when referencing it.

Use 'Calendar'[Date] directly: Update your measure to continue using 'Calendar'[Date], like this:

RevenueYTD = CALCULATE(

    [Revenue],

    DATESYTD('Calendar'[Date])

)


Then make sure your fact data is filtered correctly using a relationship or with a bridging technique like TREATAS().

Option 2: Mark 'CalendarTbl' as a Date Table

If you must use 'CalendarTbl', try marking it as a Date Table:

  • Go to Model View > Right-click 'CalendarTbl' > Mark as Date Table > select [Date].

Note: This only works if the [Date] column has unique, continuous dates.

Also, kindly refer to the below mentioned links for better understanding:
DATESYTD function (DAX) - DAX | Microsoft Learn
Set and use date tables in Power BI Desktop - Power BI | Microsoft Learn
TREATAS function - DAX | Microsoft Learn

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.