Cummulative Forecasting

#1

Hey I am using the following formulas to predict sales. These formulas work how i expect however when i try to implement a cumulative function, it breaks down.

Prediction =
Var monthlyAverage = AVERAGEX(VALUES(‘Dates’[MonthnYear]), [Scenario Agreement Gross Margin])
Var averageTotal = SUMMARIZE(‘Dates’, ‘Dates’[MonthName], “Monthly Average”, monthlyAverage)

Return
IF(HASONEVALUE(‘Dates’[MonthName]), monthlyAverage, SUMX(averageTotal, [Monthly Average]))

Production + Actuals = i
f(ISBLANK([sales this year]) || SELECTEDVALUE(Dates[MonthOfYear]) = MONTH(TODAY()), [Prediction], [sales this year)])

Cummulative Prediction Scenario =
CALCULATE([Prediction + Actuals], FILTER(ALLSELECTED(Dates), Dates[Date] <= MAX(Dates[Date])))

These are the formulas currently being used. The output can be seen in the photo

#2

Note* When i take the date back to 2016 the formula works fine as can be seen in the photo

however, this then is not a correct prediction so therefore its wrong.

#3

I just want to check here that you don’t actually need the month & year column instead of the monthname?

Because a cumulative total on Monthname when you have multiple years selected doesn’t make a lot of sense.

That formula should work with the formulas you currently have.

I’m looking to work on a solution for the problem as well. But there a bit more to it.

#4

This formula should get you want you need. It’s a bit of virtual table magic. (Still not 100% why you need this), but interested nonetheless

``````Cumulative Monthly Sales =
VAR CurrentMonth = MAX( Dates[MonthOfYear] )
VAR MinDate = CALCULATE( MIN( Dates[Date] ), ALLSELECTED( Dates ) )
VAR MaxDate = CALCULATE( MAX( Dates[Date] ), ALLSELECTED( Dates ) )
VAR DateRange = FILTER( ALL( Dates ), Dates[Date] >= MinDate && Dates[Date] <= MaxDate )

RETURN
SUMX(FILTER(
SUMMARIZE( DateRange, Dates[MonthName],
"Sales", [Total Sales],
"MonthNumber", MIN( Dates[MonthOfYear] ) ),
[MonthNumber] <= CurrentMonth ),
[Sales] )
``````

There’s a bit to this I know, and there’s likely other ways you could do it, but this is what I have come up with for now.

The reason it wasn’t working previously was due to the initial context of the calc.

This is the formula working as per below

#5

If any further questions let me know

#6

Here’s an attempt to make the formula cleaner

#7

Appreciate it Sam, it works for what I needed.

#8

That’s great. I enjoyed working on the solution

#9

Sam I think that the DAX formula for the solution above could be simplified by using the cumulative total pattern and slightly modifying it. I was able to recreate your solution using the cumulative total pattern by modifying the date filter to use month number instead of date:

``````Cumulative Monthly Sales =
CALCULATE (
[Total Sales],
FILTER (
ALLSELECTED ( 'Dates' ),
**'Dates'[Month Number] <= MAX ( 'Dates'[Month Number] )**
**    )**
)
``````

#10

Very nice indeed. Like it a lot. Yes agree, this is actually simpler

Side note: for formula inputs into forum see here - How Place DAX Formula Into Forum Topics & Posts