Rolling Quarter Average without Filter

DAX DAX Calculations
1

Hi All,

I’m hoping you can help me with this. I need to calculate what the (rolling) Average is for the last 4 (complete) quarters.

For example, I have this matrix table:

image1
image1.jpg733×351 102 KB

The values represent Total $$ Shipped in the quarter, broken down by week (13 weeks in a quarter). I need a calculation that can look back over the last 4 complete quarters (2017-Q2, 2017-Q3, 2017-Q4, 2018-Q1) and give me an average but WITHOUT using a filter.

I can get what I need if I have a filter in place. For instance, here are the correct averages if I used a filter:

image2

But I need this to be automatic so when the next quarter is complete, it adjusts and the user doesn’t have to constantly change the filter.

NOTES: The “Week Number” refers to the week number in the quarter. Also, we use a 4-4-5 fiscal calendar and yes, I have a DATE table which is set up for fiscal dates.

Thanks,
Rose

2

If been thinking a bit about this one. It’s difficult because I can’t replicate the exact scenario.

But this is the technique that will solves this I believe

Rolling Quarterly Avg. Last 4 = 
AVERAGEX( 
    TOPN( 4, VALUES( Dates[Month & Year] ), SUM( Dates[MonthnYear] ), DESC ),
        [Total Sales] )

What TOPN will do is always retrieve the last 4 quarter and years for you and then AVERAGEX will average up all the results.

Can you try to input this technique across your data with the correct inputs and see how it goes.

Intuitively this is the way to solve it, it will just require adjusting for the correct inputs.

3

Sam,

Your measure gave me the same results that my original measure did. It did not show the last 4 quarters, it showed everything UNLESS you used a filter. I know that doesn’t make sense logically because of the TOPN calc, but that’s what it did. Here’s my measure:

Rolling Quarterly Avg. Last 4 = 
    AVERAGEX( 
        TOPN( 4, VALUES( PBI_FSCAPF[YEAR-QTR] ), sum( PBI_FSCAPF[YearQtr]), DESC ),
            [Book-Ship Qtr] )

where PBI_FSCAPF[YEAR-QTR] = “2018-Q1” and where PBI_FSCAPF[YearQtr] = 20181 and where [Book-Ship Qtr] = $$ items shipped in a quarter.

I did solve this, but probably in a klunky way. I created measures for CURRENT QTR, PREV QTR, PREV QTR-1, PREV QTR-2, and PREV QTR-3. Then I added the values that would be put into each of those buckets and divided by 4. Here’s my result which now works:

Book-Ship Rolling 4 Qtr Avg = 
    (calculate([Book-Ship qtr], 
        filter(PBI_FSCAPF,PBI_FSCAPF[YEAR-QTR]=[Prev Yr-Qtr])) + 
    calculate([Book-Ship qtr], 
        filter(PBI_FSCAPF,PBI_FSCAPF[YEAR-QTR]=[Prev Yr-Qtr-2])) +
    calculate([Book-Ship qtr], 
        filter(PBI_FSCAPF,PBI_FSCAPF[YEAR-QTR]=[Prev Yr-Qtr-3])) +
    calculate([Book-Ship qtr], 
        filter(PBI_FSCAPF,PBI_FSCAPF[YEAR-QTR]=[Prev Yr-Qtr-4])))/4

This measure also ensures that the current quarter will never be included in the calculations.

Rose

1 Like
4

Ok nice one, glad you got it going. Chrs