Previous period average calculation

[TimQ]

I'm seeking to create a visual that displays annual values as a trend line along a fiscal year (text field) axis and a target line that is the calculated window average of the three fiscal year periods prior to the latest or current period. So in the image below, the calculation would average the values from FY17, FY18 and FY19, but dynamically update once a new fiscal year period is added to the data. PBIX is linked here. Thanks.

[CheenuSing]

Hi @TimQ 

If it worked please accept it as solution.

Cheers

CheenuSing

[CheenuSing · Accepted solution]

Hi @TimQ 

Based on the pbix file provided, I did the following

1. Created a calculated column called Year and set the type to number

2. Created a measure called Average

Average = 
VAR MaxYear = Calculate(MAX(Sheet1[Year]),ALL(Sheet1) )
Return
CALCULATE (    AVERAGE(Sheet1[Amount]),
                                Filter(ALL(Sheet1), MaxYear -Sheet1[Year] >= 1 &&  
                                     MaxYear -Sheet1[Year] <=3 )
                      )

3. Plotted this on the line caahrt as values.

I have attached pbix for your reference

Cheers

CheenuSing

[TimQ]

This appears to work. Thanks @CheenuSing!