I currently have the below formula that is working out the rolling avg month on month. Which is then used to minus the rolling average from the last calendar month to give me a predicition for what I should expect for the current month. The only issue is that at the beginning of every month I am having to go in and update the formula to include the last calendar month. Is there a way to update this formula so I am not having to update it, happy for the formulas to be seperated if that is easier?
New Prod Control forecast P16s =
VAR NovemberAvg =
CALCULATE(
SUM(Table[Score]) / DISTINCTCOUNT(Table[ProdNumber]),
FILTER(
'Table',
'Table[CreditDate] >= DATE(2024, 11, 01) &&
'Table'[CreditDate] <= DATE(2024, 11, 30) &&
'Table'[ID] = "Mfg Control"
)
)
VAR OctoberAvg =
CALCULATE(
SUM('Table'[Score]) / DISTINCTCOUNT('Table'[ProdNumber]),
FILTER(
'Table',
'Table'[CreditDate] >= DATE(2024, 10, 01) &&
'Table'[CreditDate] <= DATE(2024, 10, 31) &&
'Table'[ID] = "Mfg Control"
)
)
-- Calculate the average score for the last month (September)
VAR SeptemberAvg =
CALCULATE(
SUM('Table'[Score]) / DISTINCTCOUNT('Table'[ProdNumber]),
FILTER(
'Table',
'Table'[CreditDate] >= DATE(2024, 09, 01) &&
'Table'[CreditDate] <= DATE(2024, 09, 30) &&
'Table'[ID] = "Mfg Control"
)
)
-- Calculate average scores for previous months with the "Mfg Control" filter
VAR MarchAvg =
CALCULATE(
SUM('Table'[Score]) / DISTINCTCOUNT('Table'[ProdNumber]),
FILTER(
'Table',
'Table'[CreditDate] >= DATE(2024, 03, 01) &&
'Table'[CreditDate] <= DATE(2024, 03, 31) &&
'Table'[ID] = "Mfg Control"
)
)
VAR AprilAvg =
CALCULATE(
SUM('Table'[Score]) / DISTINCTCOUNT('Table'[ProdNumber]),
FILTER(
'Table',
'Table'[CreditDate] >= DATE(2024, 04, 01) &&
'Table'[CreditDate] <= DATE(2024, 04, 30) &&
'Table'[ID] = "Mfg Control"
)
)
VAR MayAvg =
CALCULATE(
SUM('Table'[Score]) / DISTINCTCOUNT('Table'[ProdNumber]),
FILTER(
'Table',
'Table'[CreditDate] >= DATE(2024, 05, 01) &&
'Table'[CreditDate] <= DATE(2024, 05, 31) &&
'Table'[ID] = "Mfg Control"
)
)
VAR JuneAvg =
CALCULATE(
SUM('Table'[Score]) / DISTINCTCOUNT('Table'[ProdNumber]),
FILTER(
'Table',
'Table'[CreditDate] >= DATE(2024, 06, 01) &&
'Table'[CreditDate] <= DATE(2024, 06, 30) &&
'Table'[ID] = "Mfg Control"
)
)
VAR JulyAvg =
CALCULATE(
SUM('Table'[Score]) / DISTINCTCOUNT('Table'[ProdNumber]),
FILTER(
'Table',
'Table'[CreditDate] >= DATE(2024, 07, 01) &&
'Table'[CreditDate] <= DATE(2024, 07, 31) &&
'Table'[ID] = "Mfg Control"
)
)
VAR AugustAvg =
CALCULATE(
SUM('Table'[MPAScore]) / DISTINCTCOUNT('Table'[ProdNumber]),
FILTER(
'Table',
'Table'[CreditDate] >= DATE(2024, 08, 01) &&
'Table'[CreditDate] <= DATE(2024, 08, 31) &&
'Table'[ID] = "Mfg Control"
)
)
-- Calculate the month-on-month differences
VAR MarchToAprilDiff = AprilAvg - MarchAvg
VAR AprilToMayDiff = MayAvg - AprilAvg
VAR MayToJuneDiff = JuneAvg - MayAvg
VAR JuneToJulyDiff = JulyAvg - JuneAvg
VAR JulyToAugustDiff = AugustAvg - JulyAvg
VAR AugustToSeptemberDiff = SeptemberAvg - AugustAvg
VAR SeptembertoOctoberDiff = OctoberAvg - SeptemberAvg
VAR OctobertoNovemberDiff = NovemberAvg - OctoberAvg
-- Calculate the average of the month-on-month differences
VAR AvgMonthlyDiff =
(MarchToAprilDiff + AprilToMayDiff + MayToJuneDiff + JuneToJulyDiff + JulyToAugustDiff + AugustToSeptemberDiff + SeptembertoOctoberDiff + OctobertoNovemberDiff) / 8
-- Return the September average minus the average of the month-on-month differences
RETURN
NovemberAvg - AvgMonthlyDiff
