Forum Discussion

CJ_96601's avatar
CJ_96601
Helper V
3 years ago

Calculated Measure

Need help, 

 

Please see below sample table:  

 

Need to get the average by getting 2021  YE of 50 + data from 2022 based on month selected.

 

Note that 2021 YE must always be the YE total and must not be affected by date filters.

 

2021Jan2     
2021Feb2     
2021Mar2     
2021Apr4     
2021May5     
2021Jun6     
2021Jul7     
2021Aug8     
2021Sep8     
2021Oct2     
2021Nov2     
2021Dec2     
 Total YE50     
        
   Avg Formula - Total YE +  data (based on month selected)
2022Jan226    
2022Feb326.5    
2022Mar427    
2022Apr527.5    
2022May628    
2022Jun728.5    
2022Jul829    

 

Thanks and regards, 

 

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    CJ_96601 , Using a separate date/year table joined to you tbale

     

    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

     

    Not you can have

     

    Last Year = (CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1)) + sum('Table'[Qty]) )/2