Calculate Weighted Score for each Account by month

[Anonymous]

I  am trying to calculate score for each account by giving weights to previous scores

Account	Score	Date
x	1	1/1/2018
x	2	2/1/2018
x	3	3/1/2018
x	4	4/1/2018
x	5	5/1/2018
y	2	1/1/2018
y	3	2/1/2018
y	4	3/1/2018
y	2	4/1/2018
y	1	5/1/2018

Table I have

What I want

Account	WeightedScore
x	5
y	10

Where  WeightedScore = 0.9*(Most recent Score by date i.e (5/1/2018)  ->  5) + 0.4(Next most recent score by date i.e (4/1/2018) -> 4)  ............................. and so on  ---- only for last 5 months

Thank you for Your Help.

[LivioLanzo]

If the most recent score has a weight of 0.9 and the second most recent score has a weight of 0.4, what is the weight of the other 3 ?

[Anonymous]

0.2, 0.15, 0.5 I am just making them up, the Idea is to use 5 different weights which add up to 1.

[LivioLanzo]

With these wights, I am getting 7.5 instead of 5:

[Anonymous]

you are right That's because I did not calculate it I was just making up the values, I wanted the logic that will get me From A - B   

[LivioLanzo · Accepted solution]

I see now. So you need to build the below model:

download the file: https://1drv.ms/u/s!AiiWkkwHZChHjzkDSa4OQLU-JEcu

 

Measure =
IF (
    HASONEVALUE ( Accounts[Account] ),
    VAR AddScore =
        ADDCOLUMNS (
            SUMMARIZE ( Scores, 'Calendar'[Year Month] ),
            "Score", CALCULATE ( MAX ( Scores[Score] ) )
        )
    VAR Top5 =
        TOPN ( 5, AddScore, 'Calendar'[Year Month], DESC )
    VAR WeightedScore =
        SUMX (
            Top5,
            VAR CurMonth = [Year Month]
            VAR Rnk =
                RANKX ( Top5, [Year Month], CurMonth, DESC )
            RETURN
                [Score]
                    * CALCULATE ( VALUES ( Weights[Weight] ), Weights[Recent] = Rnk )
        )
    RETURN
        WeightedScore
)

[Anonymous]

Thanks a Lot, That was really helpful!