topic Re: AVG year to date, starting from december in DAX Commands and Tips

AVG year to date, starting from december

Lars_c — Wed, 30 Jun 2021 07:56:03 GMT

Hi everyone,

A customer has a series of very specific calculations for some of the Year-to-date values.

Right now, for most of the values, i calculate the average between the values with this formula:

[MTH] are the monthly values, 'Dates' is my date-table.

Calculate( Averagex (Dates, [MTH] ),

Filter(all(Dates), Dates[Dates] <= max (Dates[Dates]) && Dates[Year] = max(Dates[Year])

))

This returns for March: (Jan+Feb+March)/3

My problem starts with another KPI, which uses the value from December of the previous year as well.

Example for March: (Dec(LY)+Jan+Feb+March)/4

Note, for december in the current year, the calculation goes: (Dec(LY)+Jan+Feb+...+Nov+Dec)/13

I can't seem to get to my formula starting from december, any help?

Re: AVG year to date, starting from december

Anonymous — Wed, 30 Jun 2021 16:50:13 GMT

Lars_c

It looks to me that the formula you've shown calculates a daily average, not a monthly. You are iterating the Date table in AVERAGEX, so you're iterating on the daily granularity, not the monthly. Why do you say it calculates some kind of monthly average?

Re: AVG year to date, starting from december

CNENFRNL — Wed, 30 Jun 2021 23:06:00 GMT

DATESYTD( Calendar[Date], "11/30" )

Re: AVG year to date, starting from december

Lars_c — Thu, 01 Jul 2021 07:45:32 GMT

Hello Anonymous

I do filter it with 'calculate', which results in only one value each month. I only have values for one day of the month since month is the smallest granularity.

The problem seems to me with my filter evaluation, where i say:

&& Dates[Year] = max(Dates[Year])

It should contain the month before as well.

Re: AVG year to date, starting from december

Anonymous — Thu, 01 Jul 2021 13:58:42 GMT

Lars_c

Even though you're working with the month granularity, your Dates table should contain all days. Then, you can hide the granularity below the month but it's imperative for time-intel in PBI to work correctly that the table be on the day granularity. If you ignore this rule today, you'll just shifting the refactoring/resolution into the future. And then it may be much harder to refactor. But it's up to you 🙂

To do what you want, you should number your months within the year from 1 to 12. Once you have this ordering, it's rather easy to write a measure that will include December last year (together with any conditional logic you want).

The filter you need will then look something like:

var MaxVisibleMonth = MAX( Dates[MonthNumber] ) var MaxVisibleYear = MAX( Dates[Year] ) var Result = CALCULATE( [Your Measure], filter( ALL( Dates[MonthNumber], Dates[Year] ), ( Dates[MonthNumber] <= MaxVisibleMonth && // Year must be an int. If it's // not, then you have to have // an int column that will store // the year as an int. Dates[Year] = MaxVisibleYear ) || ( Dates[MonthNumber] = 12 && Dates[Year] = MaxVisibleYear - 1 ) ), REMOVEFILTERS( Dates ) ) return Result