AVG year to date, starting from december

[Lars_c]

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?

[CNENFRNL]

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

[Anonymous]

@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?

[Lars_c]

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. 

[Anonymous · Accepted solution]

@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