cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors