DATESYTD function

[mp390988]

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 you can see:

However, when I change the table referenced in my measure from CalendarTbl to Calendar so its now like this:

RevenueYTD = CALCULATE(

    [Revenue],

    DATESYTD('Calendar'[Date])

)

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:

CalendarTbl = SELECTCOLUMNS(

    'Calendar',

    "Date", 'Calendar'[Date],

    "Day", 'Calendar'[Day],

    "MonthNumber", 'Calendar'[MonthNumber],

    "MonthName", 'Calendar'[MonthName],

    "Year", 'Calendar'[Year],

    "Qtr", 'Calendar'[Quarter],

    "IsHoliday", 'Calendar'[IsHoliday],

    "WorkingDaysPerMonth", 'Calendar'[WorkingDaysPerMonth]

)

This is my model:

Question is, why does this measure not work?

RevenueYTD = CALCULATE(

    [Revenue],

    DATESYTD('CalendarTbl'[Date])

)

[v-kpoloju-msft]

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.

[wardy912]

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!

[FBergamaschi]

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

[mp390988]

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

[FBergamaschi]

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

[mp390988]

Hi @FBergamaschi ,

Apologies, I did not make it clear.
There is no relationship between Calendar and TradesTbl.

When I said the measure works when referencing Calendar I realised the values it was returning were simply the sum of revenue across all years i.e. basically just returning the sum of revenue which is not what I wanted.

The reason why I need to use CalendarTbl and not the original Calendar is because I need to append some calculated columns to Calendar but Calendar is coming via a direct query, so hence why I created a calculated table called CalendarTbl using SELECTCOLUMNS and then to this table I was able to add my calculated columns to help me further with my analysis. But this CalendarTbl, despite being marked as a DATE table and ensuring the date field is of the type "Date" and not "Date/Time" and also ensuring the foreign key in the TradeTbl is also of the type "Date" where these two tables connect, I am still not able to get any values return from my measure.

Thank You 

[FBergamaschi]

OK, can you please share the model? I need to check it. It should perfectly work, but I need to open it. I shall send you a provate message now.

[mp390988 · Accepted solution]

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

[FBergamaschi]

Ok please mark the solution and kudos to whom helped you please

Thx