Store Results of a measure without results being attached to the dates that helped calculate it

This is a very strange one, but I’ve actually had to deal with this issue several times and I’m not sure how to do it.

This is a DAX problem, but I’m going to use Excel to illustrate:

I need to calculate Average Sales by WEEK over 3 previous quarters. My fiscal calendar has 13 weeks; we’re on a 4-4-5 fiscal calendar so there are 13 weeks in each quarter. I have this measure and it works well.

My problem is this: For example, if we’re in Week 12 in the CURRENT quarter, I need to take the average for week 12 for the previous 3 quarters (which is $712,616) and display it for the current month. When I try to do this, I get 0’s, because the measure is only going to show the values for week 12 for each of the actual quarters where the sales occurred (2017-Q4 12/24-12/30, 2018-Q1 3/18-3/24, 2018-Q2 6/17-6/23).

I need to take that weekly average $712,616 and save it so it’s not attached to the dates that helped calculate it and show it in the current month. See below.

I tried creating a separate date table, but it got very messy and ultimately couldn’t get it to work.

Any suggestions?

Hi Rose,

Is it possible for me to test this on your example.

There’s always a few nuances when dealing with 445 calendars.

Also what current formulas are you using?

I don’t think you require a different date table, it will just be able re-arranging the virtual table inside of say AVERAGEX. Which is the formula I presume your using for this?

I think I solved the problem. As soon as I realized that I had to treat this the same as a “rolling month”, I was able to make it work. Here is my measure:

Current Rolled Book Ship Estimate = CALCULATE([Current Book Ship Estimate],
                                           FILTER(ALL(PBI_FSCAPF),
                                                (PBI_FSCAPF[LUDATE] <= MAX(PBI_FSCAPF[LUDATE])
                                                 ))
                                                 )

where PBI_FSCAPF is my DATE table and [LUDATE] is my DATE field.

Ok that’s great if it works.

That to me just looks like a Cumulative Total calc…is that what you wanted?

I thought you initially wanted a rolling average? Usually you would use AVERAGEX for those types of calculations.