cancel
Showing results for
Did you mean:
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

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(
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``````

4 REPLIES 4
Super User
``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

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?

Frequent Visitor

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

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(
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``````

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors