Help with rolling 4 week and 13 week averages from a measure

[mcaldwell]

I've looked all around and tried many options found in the forums but cannot get anything to work.

I have a matrix showing spend filtered to one category (out of ten),

 

 the dates are Start of Week from my Date Table and 

the amounts come from the following measure 

Spend By Category Actual = CALCULATE(SUM(supplier_report_items[Subtotal Corrected]) + SUM(supplier_report_item_surcharges[Subtotal Corrected]))

What I need are two measures I can use in cards and most likely other visuals.  

 

• First Measure is the rolling weekly average of the prior Fiscal Month.  In this case the date range would be 5-29-23 to 6-30-23 with a total of 5 weeks.
• Second Measure is the rolling weekly average of the 2 Fiscal Months prior to the First Measure.  In this case the date range would be 4-3-23 to 5-27-23 with a total of 8 weeks.

I tried using the original calculation in the Spend by Category measure but I end up not being able to filter to the Category I need and the averages are way off and every solution from the forums I tried either gave me the wrong amounts, a blank value or an error.

Any help would be appreciated!

[Chris_White · Accepted solution]

I would break the problem into two.  First look at prior fiscal month calculations, then rolling average calculations.

Are you thinking of prior fiscal month as always being from today or (for example) are you allowing users to filter the report to May, in which case the prior fiscal month is April?  If the former, can you make an isPriorFiscalMonth column in your date table?  This would be a boolean with the idea that you filter on this later.  If the latter can you add PriorFiscalMonth and PriorFiscalMonthYear columns to your date table?  For example, for 12/05/2023 these might be 4 & 2023 and for 12/01/2023 these might be 12 & 2022 (depending on how your fiscal periods work).

Once you've got these in place you should have a much easier time of it doing your rolling averages as you will just be doing some simple filters rather than simultaneously having to do a load of date-math.

[mcaldwell]

@Chris_White While I had to jump through additional hoops to get to my final answer your suggestion put me on the right track.  Thank you!!