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

image


#2

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

image

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

image


#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