Forum Discussion
Previous period average calculation
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.
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
3 Replies
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
This appears to work. Thanks Anonymous!
