Forum Discussion

TimQ's avatar
TimQ
Helper I
7 years ago
Solved

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

    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

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
TimQ
Helper I
7 years ago

This appears to work. Thanks Anonymous!