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

[Roseventura]

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:  I need to take the average for week 12 for the previous 3 quarters (which is $712,616) and display it in 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 occured (2017-Q4 12/24-12/30, 2018-Q1 3/18-3/24, 2018-Q2 6/17-6/23).

I need to take that weekly avarege $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?

[v-shex-msft]

HI @Roseventura,

Can you please share some sample data to test and coding formula?

Regards,

Xiaoxin Sheng

[Roseventura · Accepted solution]

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.