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

DAX DAX Calculations
1

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?

Capture3.JPG
Capture3.JPG841×408

2

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?

3

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.

4

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.