Forum Discussion

ahjuang's avatar
ahjuang
New Member
7 years ago
Solved

Weighted Result based on Recent Month

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    ...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    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

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
7 years ago

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