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

Vote for your favorite vizzies from the Power BI Dataviz World Championship submissions. Vote now!

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
Power BI DataViz World Championships

Power BI Dataviz World Championships

Vote for your favorite vizzies from the Power BI World Championship submissions!

Sticker Challenge 2026 Carousel

Join our Community Sticker Challenge 2026

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

January Power BI Update Carousel

Power BI Monthly Update - January 2026

Check out the January 2026 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.