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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
IsaacAsher
Helper I
Helper I

How to get previous year data for the first year of filtered data

(Apologies if this gets double posted.... I posted once but it is not showing in the forum after 20+ minutes it still isn't showing, so posting a second time on the assumption I did something wrong or accidentally backed out of it.)

 

Trying to use a measure 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. Since this formula looks at the prior year, it is blank for the first year of data as filtered by the end user. I want this formula to calculate the average of [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 on the results. 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 see.png

And here is what I'm aiming for:

What I want.png

Thanks in advance

 

3 REPLIES 3
IsaacAsher
Helper I
Helper I

I continue to research.   It seems like I'm missing something with changing context, but could really use some pointers.  Thanks in advance. 

stevedep
Memorable Member
Memorable Member

Can you share a sample of your source data so we can work from there, also the data model.

I am uncertain what you are asking for when you want to see a sample of the source data.  There is sensitive information in the data, so I need to be careful about what I am sharing.  Could you please be a little more specific?  Sorry, still a noob.  

 

Here is the data model: 

 

data model.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors