Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
(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:
And here is what I'm aiming for:
Thanks in advance
I continue to research. It seems like I'm missing something with changing context, but could really use some pointers. Thanks in advance.
Can you share a sample of your source data so we can work from there, also the data model.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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:
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |