Forum Discussion
retrieving average value for base year, by segments
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:
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)
Many thanks for your help !
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.
5 Replies
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.
