Cumulative Forecasting

DAX DAX Calculations
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.

image
image.png518×503 31.6 KB

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

image
image.png745×766 60.4 KB

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] )**
**    )**
)

image
image.png718×479 43.8 KB

1 Like
10

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

Side note: for formula inputs into forum see here - https://forum.enterprisedna.co/t/how-place-dax-formula-into-forum-topics-posts/156/3

1 Like