Weighted average based only on 3 latest date points

[valeriydik]

Hi all,
could you please help me with the case below?

I want "WEIGHTED PD0" to be an average value of "%PD 0 dpd" weighted by "*Balance bucket 0" (screen 1). The thing is that the weighted average must be calculated for any dimension ("cycle_name" is just example) and it also must work as on Excel screen 2, i.e. be summed up for only 3 latest points of report_dt (report_dt is always an end-of-month date).

Have you got any ideas?

[v-sihou-msft · Accepted solution]

@valeriydik

In your scenario, since you need a cumulative calculation for your "weighted avg", you must have an index column in your source table. Then you can write a measure like below:

Measure =
CALCULATE (
    SUMX ( Table1, [%pd 0 dpd*] * [Balance Bucket*] )
        / SUM ( Table1[Balance Bucket] ),
    FILTER ( ALL ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
)

Regards,