DatesYTD not working on calculated Calendar table

[SevsBo]

The longer tail of this question is that I am trying to create a YTD this year vs YTD last year comparison, so I am following instructions on how to build that. One of the requirements is to have a Calendar table that can be used as the basis for filtering MainTable, if we call it that, but when testing the Measure just for this year TYD I already hit a problem.

The Calendar table has an active relationship to MainTable and if I add a time slicer to the page, I can use Calendar date to filter results from MainTable, or I can use a Filter on this visual/page to filter the date, so the relationship works. But this relationship appears to stop working in the Measure.

I have already checked the data in Query Editor for MainData - the Date column is set to Date. I have also used "Mark as date table" on the calculated Calendar table.

My test measures, for this year YTD, are:

Measure1 = 

CALCULATE(COUNTROWS(MainTable), MainTable[Codition1] = "one", MainTable[Codition2] = "two", MainTable[Codition2] = "three", DATESYTD(MainTable[Date]))

Measure2 = 

CALCULATE(COUNTROWS(MainTable), MainTable[Codition1] = "one", MainTable[Codition2] = "two", MainTable[Codition2] = "three", DATESYTD(Calendar[Date]))

Measure1 works perfectly while Measure2 shows BLANK as the result.

Any advice on how to resolve this?

[v-zhangtin-msft]

Hi, @SevsBo 

I reverted your problem but my measure2 is working fine, please check the attachment.

Can you provide some of the example data? Please remove any sensitive data in advance.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SevsBo]

Hi @dharmendars007 

As stated in the original question, there is an active relationship, and the Calendar table does work as a filter outside the measure. Is there an issue with the measure?

[dharmendars007]

Hello @SevsBo , 

Kindly check if your Calendar table and Main Table is connected with one-to-many relationship and it is active.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN