Weighted Result based on Recent Month

[ahjuang]

Name	Month	Rating(1-5)		Current  Month = 60% weighted
Safety	January	1		-1 Month = 30% Weighted
Safety	February	1		-2 Month = 10% Weighted
Safety	March	5
Safety	April	5		March's  Safety Rating =	3.4
Collaboration	January	5		April's Safety Rating =	4.6
Collaboration	February	1
Collaboration	March	5		March's Collaboration Rating =	3.8
Collaboration	April	4		April's Collaboration Rating =	4

In Dax, anyone can tell me how to get the result of the march's safety and collaboration rating when March is select,  and when April is selected, April result get output or what would be the best solution?

[dedelman_clng · Accepted solution]

Step 1 - convert your "month" column to a date (first of the month); create a relationship to a date table

Step 2 - 3 measures

PM Rating = CALCULATE(SUM(Weighted[Rating]), PREVIOUSMONTH(Weighted[Dt]))

PPM Rating = CALCULATE(SUM(Weighted[Rating]), PREVIOUSMONTH(PREVIOUSMONTH(Weighted[Dt])))

This Month Rating = SUM(Weighted[Rating])*.6 + [PM Rating]*.3 + [PPM Rating]*.1

You may want to do some "IF (ISBLANK ( " logic in the measures so that you don't get values for months that don't have 2 previous months.

Hope this helps,

David