YTD gets calculated in wrong order

[phjz]

I had a YTD calculation which worked well so far:

YTD = CALCULATE(AVERAGEX(SUMMARIZE('Data','Data'[CW],"Weekly",[Old_Value_Measure]),[Weekly]),FILTER(all(Calendar),Calendar[Year] = MAX(Calendar[Year]) && Calendar[Date] <= MAX(Calendar[Date])))

Now, I have to do some calculation with the Value-Measure for every week: New_Value_Measure = Old_Value_Measure * Total_Universe / Entitled_Universe. Both universe values have also numbers for every week with Total_Universe = CALCULATE(AVERAGE(

'Total Universe'[Wert]),FILTER(...)), Entitled_Universe works the same. The aggregation doesn't matter on a weekly base. So the table looks like this:

Week	Old_Value_Measure	Total_Universe	Entitled_Universe	New_Value_Measure	YTD_New
2024-01	10,22	1262119	567092	22,74	22,74
2024-02	63,87	1251453	554581	144,12	83,01

Now, the formula for YTD_New produces wrong numbers:
YTD = CALCULATE(AVERAGEX(SUMMARIZE('Data','Data'[CW],"Weekly",[YTD_New]),[Weekly]),FILTER(all(Calendar),Calendar[Year] = MAX(Calendar[Year]) && Calendar[Date] <= MAX(Calendar[Date])))

And I know why: The formula calculates the Average for Old_Value_Measure multiplies it with the Average for Total_Universe and divides it with the averarge for Entitled_Universe: 37,04 * 1256786 / 560837 = 83,01

But, what I want to do is to calculate the average of the calculated New_Value_Measures per week, so it has to be: 22,74  + 144,12 / 2 = 83,43

But unfortunately, I have absolutely no clue how to do this in Power BI. Does anyone have an idea?

Thanks a lot!

[v-jialongy-msft]

Hi @phjz 

Please try the following DAX:

MEASURE =
CALCULATE (
    SUM ( 'Table'[New_value] ) / COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date].[Year] = MAX ( 'Calendar'[Date].[Year] )
            && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Best Regards,

Jayleny

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

[phjz]

Thanks for your reply, but that doesn't work for me, because New_Value is a measure: New_Value_Measure = Old_Value_Measure * Total_Universe / Entitled_Universe

So, I can't sum up this measure....

Any other ideas?
Thanks!