Forum Discussion
DATESYTD function
Hello,
I have a measure defined as:
When I drop this measure into my visual, it does not show any values, as you can see:
However, when I change the table referenced in my measure from CalendarTbl to Calendar so its now like this:
it works and returns values:
My model includes a direct query to the Calendar table which lives inside another power bi semantic model.
I created a calculated table called CalendarTbl using the following DAX:
This is my model:
Question is, why does this measure not work?
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
9 Replies
To answer your question I need to see the entire data model and I need answer to the following
1 - the [Revenue] measure calculated revenues of course, but what is the table where this calculation happens (the table containing the revenues lines)?
2 - is the above table connected both to Calendar and to CalendaTBL? With which kind of relationship? Using which field?
Best
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi FBergamaschi ,
1). In the diagram I have provided that shows my model, you will see a table called TradeTbl. This is where Revenue comes from. I don't have another table called Revenues. The TradeTbl is the only fact table.
2). This fact table is connected to my CalendarTbl using a 1-to-many relationship with a single filter flowing from the CalendarTbl to TradeTbl.Thank You
Thank you but still you did not specify how Calendar is connected to the table TradesTbl
If the measure works using Calendar, there must be a relationhip between Calendar and TradesTbl that I do not see
Further, what colum connects Calendar to TradesTBL and what CalendarTbl to Trades Tbl?
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
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.
- Use the original 'Calendar' table in your measure.
- 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!
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.
