topic Re: retrieving average value for base year, by segments in DAX Commands and Tips

retrieving average value for base year, by segments

dp_75 — Fri, 18 Oct 2024 21:29:54 GMT

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=zu1hr8in8kqj7p96l8h3gnmtp&st=k81su0cq&dl=0

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

Re: retrieving average value for base year, by segments

lbendlin — Sat, 19 Oct 2024 14:23:30 GMT

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.

Re: retrieving average value for base year, by segments

dp_75 — Sun, 20 Oct 2024 12:27:32 GMT

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=zu1hr8in8kqj7p96l8h3gnmtp&st=fgxxdfm1&dl=0

Re: retrieving average value for base year, by segments

lbendlin — Sun, 20 Oct 2024 21:04:45 GMT

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

Re: retrieving average value for base year, by segments

dp_75 — Tue, 22 Oct 2024 09:58:17 GMT

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.

Re: retrieving average value for base year, by segments

lbendlin — Tue, 22 Oct 2024 12:42:36 GMT

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