3 month rolling average with Last Value

Question

This is my problem. I am trying to calculate 3 month average every month (rolling) with last value.

For example:


ID   SCORE DATE 

2           3      3/19

1          * 2      4/19

6           3      5/19            May(avg Mar,Apr,May)  3+*2+3+4/4 =   3

3           4      5/19

4           4      6/19            June(avg April May June) 3+4+4+*3+5/5= 3.8

1          * 3      6/19

6           5      6/19

So May average would be would be 3 (12(Mar,Apr,May)/4) and the value for ID 1 Score=2

But in June average would be would be 3.8 (19(Apr,May,Jun)/5) and the value for ID 1 Score=3.

I tried to follow this https://community.powerbi.com/t5/Desktop/Rolling-3-Month-Average/m-p/695325#M335440) , what I need is the way to filter or pass only the lastest values to be used for sum.

Rolling 3msc =
 VAR PeriodEnd =
    LASTDATE ( 'Table'[Date] )
 VAR PeriodStart =
    FIRSTDATE ( DATESINPERIOD ( 'Table'[Date], PeriodEnd, -3, MONTH ) )
 RETURN
    CALCULATE (
        SUM ( 'Table'[Score] ),
        DATESBETWEEN ( 'Table'[Date], PeriodStart, PeriodEnd )
    ) # what I need to figure out how to sum the score based 
        on the last score in this period
 / CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    DATESBETWEEN ( 'Table'[Date], PeriodStart, PeriodEnd )
)

I'm guessing that your problem is that most of these Date and Time functions need a continuous series of dates. I.e. you need to create a Calendar with all dates ranging from your first date with data to your last date with data (at least). Have a 1:* relationship between the Calendar and the data table and use Calendar[Date] instead of Table[Date] — OscarLar
Hi thank you for your answer. I do have a separate date table. In this case I use same table to pick up last three month value(so last date form the data set and previous 3 months) .This works as a rolling average but I don't think is the case of date restriction but rather sub-setting the calculation to pick up only the most recent value to perform sum... I think — Hector Escaton
I'm not sure I understand what's going on with the asterisked [Score] values. And what do you mean by ID 1=3? — greggyb
What I am trying to achieve is the average of the last score of the user . So ID 1 score in May would be 2(May(avg Mar,Apr,May) 3+*2+3+4/4 = 3) and in June the score for ID 1 would 3 (June(avg April May June) 3+4+4+*3+5/5= 3.8). As both 3 month avg would had April in the calculation but only consider the most recent score of the user for the sum. I managed to chive part of this by using DISTINCTCOUNT in the last part of this formula, but I am not sure how to past this argument to sum. — Hector Escaton

Aldert Aldert · Accepted Answer · 2019-08-31T15:04:57

The example from microsfot is not correct, you can add a column:

Rolling 3msc = 
    var periodEnd = LASTDATE(DATESINPERIOD(AveMonths[DATE];AveMonths[DATE];1;MONTH))
    var periodStart = FIRSTDATE(DATESINPERIOD(AveMonths[DATE];periodEnd;-3;MONTH))
    return 
        CALCULATE(AVERAGE(AveMonths[SCORE]);
             FILTER(AveMonths; AveMonths[DATE] >= periodStart && AveMonths[DATE] <= periodEnd))

End result:

Here you can see the average for April is (3+2+4)/3 = 3. (March + April + May).

It does not matter how many values you have in the month, it will still work out the average..

3 month rolling average with Last Value

1 Answers