Total YTD does not work

Question

I have a table which I use to override a SUM measurement. This works fine in overriding the Sum measurement. However when I apply DATESYTD function on this measurement, I get incorrect results. My model is setup correctly to support time-intelligence functions. For 2020 which does not have a manual override value, the DATESYTD function is working fine. Below is the Dax with my results. It is required that I have YTD value. I also tried TOTALYTD function and got the same results. Any ideas to make this work?

#New Term Policy Override = Sum(NewTermOverride[NewTermCount])

#New Term Policy Prelim = SUM('Fact_Policy'[NewTermCount])

#New Term Policy = If (Isblank('Fact_Actual'[#New Term Policy Override]),'Fact_Actual'[#New Term Policy Prelim],'Fact_Actual'[#New Term Policy Override]) This formula is getting the correct value.

#New Term Policy YTD = CALCULATE([#New Term Policy],DATESYTD('Calendar'[Date])) This formula is getting the incorrect value.

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;Let me know if this works&nbsp;Measure = CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;[#New&nbsp;Term&nbsp;Policy],&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;Calendar&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar'[Date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;=&nbsp;MAX&nbsp;(&nbsp;'Calendar'[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;YEAR&nbsp;(&nbsp;'Calendar'[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;YEAR&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;'Calendar'[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;Regards,Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

anonymous · Answer

Thanks for the suggestion. I got the same result. It is not working.Any other ideas?

anonymous · Answer

Anonymous&nbsp;,&nbsp;What i understand from your visual is taht Report Date is not part of the Date Table.&nbsp;You need to pull the Date from the Date table in the visualization.&nbsp;Regards,HN&nbsp;Can you share your model and some sample date in text format.

anonymous · Answer

It appears what is happening is Power BI is not letting 2 different Date relationship from 2 seperate table to exist in one YTD calculation.&nbsp;Does that make sense?

anonymous · Answer

Anonymous&nbsp;,&nbsp;&nbsp;You will need to use USERELATIONSHIP&nbsp;https://www.youtube.com/watch?v=LfVDUiU8vaU&nbsp;&nbsp;https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bihttps://radacad.com/datesinperiod-vs-datesbetween-dax-time-intelligence-for-power-bihttps://radacad.com/building-a-virtual-relationship-in-power-bi-basics-of-treatas-dax-function&nbsp;Regards,HN&nbsp;

Forum Discussion

Total YTD does not work

5 Replies

Related content

Onedrive automatic update does not work

Power BI / Salesforce Report Connector - Does it even work???

Refreshing semantic model does not work

How does matrix column grand total work?

Total Sales per Working Days Current Year vs Total Sales per Working Days Previous Year