Sales averages for "asymmetrical" grouped years

[SimonHarris]

Hi all , my fist message in this great forum!   

I'm trying to build a table & visuals for revenus by year.  The challenge I have is : I need to display the average yearly sales for a groupe of dates ( 2017-2018-2019) compared to singles years totals (2020, 2021, 2022, 2023).  I know that I could use an averrage function for all since single years yould be divided by one and not affect the results, but dealing with the grouped dates goes beond my current DAX expertise.  I'm using a date table. 

Thank you in advance!

Simon 

[SimonHarris]

Many thanks! 

[amitchandak · Accepted solution]

@SimonHarris , You can create measures like

calculate(Averagex(Values(Table[Year]), calculate(Sum(Table[Value])) ), Table[Year] in {2017,2018,2019})

calculate(Averagex(Values(Table[Year]), calculate(Sum(Table[Value])) ), Table[Year] in {2020, 2021, 2022, 2023})

or you can use rolling

Rolling 4= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-4,year))

Rolling 4= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-48) ,-3,year))

You can also try window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc