topic Re: How to get previous year data for the first year of filtered data in DAX Commands and Tips

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

IsaacAsher — Thu, 04 Jun 2020 16:08:09 GMT

(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

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

stevedep — Thu, 04 Jun 2020 17:29:08 GMT

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

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

IsaacAsher — Thu, 04 Jun 2020 18:40:39 GMT

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:

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

IsaacAsher — Thu, 11 Jun 2020 17:41:02 GMT

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