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
IsaacAsher
Helper I
Helper I

How to calculate prior year data for first year of filtered data

Trying to calculate the average of a value over the entire previous year, and compare it to the same value for every month shown in the visual.  MyCal is my date table, created using CALENDARAUTO(). Batches is my fact table.

 

Here is the formula I am currently using:

 

CL = CALCULATE(AVERAGE(Batches[RR]), PREVIOUSYEAR(MyCal[Date]))

This works beautifully, and returns the expected value however this formula looks at the prior year, so it is blank for the first year of data as filtered by the end user (see screenshots below). I want this formula to calculate the average of Column [RR] over the previous year regardless of what year(s) the user selected in the report filter.

I also tried this:

 

CL = CALCULATE(AVERAGE(Batches[RR]), ALL(MyCal[Date], PREVIOUSYEAR(MyCal[Date].[Date]))

 

but it had no effect. The first year of data remains blank.


I have re-created the results in Excel so I can remove sensitive data and to clearly show the desired result set.

 

Here is what I'm currently getting:

What I'm seeingWhat I'm seeing

 

And here is what I'm aiming for:

What I want to see!What I want to see!

 

Any assistance is much appreciated in advance! 

1 ACCEPTED SOLUTION
technolog
Super User
Super User

The main challenge here is that the PREVIOUSYEAR function is context-sensitive, meaning it will respect any filters applied to the report. So, if a user filters to only see 2019 data, the PREVIOUSYEAR function will not have any 2018 data to work with, and thus the calculation for the first year in the filtered context will be blank.

To get around this, you need to remove the filter context on the year, calculate the previous year's average, and then apply that value to the current year's data.

Here's a way to do it:

First, let's get the minimum year from the current filter context:

MinYear = MIN(MyCal[Year])
Now, let's calculate the average of the previous year, but we'll override the filter context to only consider the year just before the MinYear:

CL =
VAR MinYear = MIN(MyCal[Year])
RETURN
CALCULATE(
AVERAGE(Batches[RR]),
ALL(MyCal),
MyCal[Year] = MinYear - 1
)
What this formula does is:

It first determines the earliest year in the current filter context.
Then, it calculates the average of Batches[RR] for the year just before that minimum year.
The ALL(MyCal) function is used to remove any filters on the date table, ensuring that the subsequent filter (MyCal[Year] = MinYear - 1) is the only one applied.
This should give you the desired result where the first year in any filtered context shows the average of the previous year.

View solution in original post

1 REPLY 1
technolog
Super User
Super User

The main challenge here is that the PREVIOUSYEAR function is context-sensitive, meaning it will respect any filters applied to the report. So, if a user filters to only see 2019 data, the PREVIOUSYEAR function will not have any 2018 data to work with, and thus the calculation for the first year in the filtered context will be blank.

To get around this, you need to remove the filter context on the year, calculate the previous year's average, and then apply that value to the current year's data.

Here's a way to do it:

First, let's get the minimum year from the current filter context:

MinYear = MIN(MyCal[Year])
Now, let's calculate the average of the previous year, but we'll override the filter context to only consider the year just before the MinYear:

CL =
VAR MinYear = MIN(MyCal[Year])
RETURN
CALCULATE(
AVERAGE(Batches[RR]),
ALL(MyCal),
MyCal[Year] = MinYear - 1
)
What this formula does is:

It first determines the earliest year in the current filter context.
Then, it calculates the average of Batches[RR] for the year just before that minimum year.
The ALL(MyCal) function is used to remove any filters on the date table, ensuring that the subsequent filter (MyCal[Year] = MinYear - 1) is the only one applied.
This should give you the desired result where the first year in any filtered context shows the average of the previous year.

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