Forum Discussion
retrieving average value for base year, by segments
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.
which values do you think are incorrect? Remember that 2024 isn't complete yet.
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.
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])-1Many thanksdp_75which values do you think are incorrect? Remember that 2024 isn't complete yet.
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.
