Total YTD does not work

[Anonymous]

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.

[harshnathani]

Hi @Anonymous ,

Let me know if this works

Measure = CALCULATE (
    [#New Term Policy],
    FILTER (
        ALL ( Calendar ),
        'Calendar'[Date]
            <= MAX ( 'Calendar'[Date] )
            && YEAR ( 'Calendar'[Date] )
                = YEAR (
                    MAX ( 'Calendar'[Date] )
                )
    )
)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Thanks for the suggestion. I got the same result. It is not working.

Any other ideas?

[harshnathani]

@Anonymous ,

What i understand from your visual is taht Report Date is not part of the Date Table.

You need to pull the Date from the Date table in the visualization.

Regards,

HN

Can you share your model and some sample date in text format.

[Anonymous]

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.

Does that make sense?

[harshnathani]

@Anonymous ,

You will need to use USERELATIONSHIP

https://www.youtube.com/watch?v=LfVDUiU8vaU

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

https://radacad.com/datesinperiod-vs-datesbetween-dax-time-intelligence-for-power-bi

https://radacad.com/building-a-virtual-relationship-in-power-bi-basics-of-treatas-dax-function

Regards,

HN