retrieving average value for base year, by segments

[dp_75]

Hi there,

Can anyone help with a query related to my previous one...trying to retrieve a calculated average value for a base year,  next to the selected later dates, and to be compatible with filters and selections.  eg. For compare Aug-24 Average value to Average Value for Aug-19, or for Jan-Aug 2024 Average compared to Average for Jan-Aug 2019, and to pull the same for Category (in this case the poast code)

See sample .pbix and data:

https://www.dropbox.com/scl/fi/kvtskvaur4ln37t8n7hob/power-BI-sample.pbix?rlkey=zu1hr8in8kqj7p96l8h3...

Using the following DAX brings back the correct 2019 Avg value for a month (but not a period eg Jan-Aug 2019), and also brings out a constant value by post code ? (see below picture)

Calc 2019 Avg = AVERAGEX( FILTER(ALL(Sheet1),Sheet1[year]=2019&&Sheet1[month]=MAX(Sheet1[month])),[Value])

 Many thanks for your help !

DP_75

[lbendlin · Accepted solution]

If you want to do date math you will want to add a calendar table to your data model.

Calc 2019 Avg = var yd = 2019-max('Calendar'[Year]) return CALCULATE(AVERAGE(Sheet1[value]),DATEADD('Calendar'[Date],yd,YEAR))

see attached.

[lbendlin · Accepted solution]

If you want to do date math you will want to add a calendar table to your data model.

Calc 2019 Avg = var yd = 2019-max('Calendar'[Year]) return CALCULATE(AVERAGE(Sheet1[value]),DATEADD('Calendar'[Date],yd,YEAR))

see attached.

[dp_75]

Thanks Ibedlin ! That works for calculating the Avg in year by Category (in this case post code).  But what about calculating the % change from 2019 Avg by Category ?  In the attached this didn't pull back correct values ? using :

Calc % vs 2019 Avg = divide( average(Sheet1[value]),[Calc 2019 Avg])-1

Many thanks

dp_75

https://www.dropbox.com/scl/fi/kvtskvaur4ln37t8n7hob/power-BI-sample.pbix?rlkey=zu1hr8in8kqj7p96l8h3... 

[lbendlin · Accepted solution]

which values do you think are incorrect?  Remember that 2024 isn't complete yet.

[dp_75]

Thanks Ibendlin - solution works for comparing years to 2019 base year, but as you say will not work for part year 2024.  Will need another solution to detected year to date 2024 data and compare to same year to date position for 2019, or full 2019 base year. 

[lbendlin]

My solution works for YTD - that should be acceptable.  No point in comparing partial 2024 to full 2019.