Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lars_c
Frequent Visitor

AVG year to date, starting from december

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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

 

 

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

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

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
Not applicable

@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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors