Sales forecasting with Ramp Up Formula Part 2

Hello All,

@BrianJ was kind enough to help me with the first requirement on this thread. He was able to build out monthly discounts by period showing a ramp up in sales for each additional period based on a start date.

Now that this is built, I am unable to total the sales by period. I have included an excel spreadsheet to show what I have currently and what I would like to add to the analysis.Sales Projections Example.xlsx (15.0 KB)

Mark

@msedlak,

Given my familiarity at this point with your use case, I’m happy to take this one as well. Thanks very much for the mockup – that gives me full clarity as to what you’re looking for. Should have a solution back to you later this evening, since this builds directly on what we’ve already done.

– Brian

@msedlak,

Okay, think I’ve got a solution for you on this one as well. Let’s start with the totals first, since that ended up being the harder part - once we get that, you’ll see the stages calculation just becomes a drag-and-drop.

The challenging aspect of correcting totals in a matrix is that you have four different evaluation contexts to deal with and correct. The way I handled this was with a SWITCH(TRUE() ) construct to manage the four different combinations of HASONEVALUE = TRUE/FALSE for the row and column fields of the matrix, using a CROSSJOINed virtual table to simulate the matrix visual structure. It’s a long measure, but if you break it down into the A, B, C and D sections of the visual below, I think the logic is pretty clear, and I’ve added some comments to help you navigate it.

Discounted Monthly Sales w/ Totals by Client = 

VAR AllClientDiscount =
LOOKUPVALUE(
    Discounts[Discount],
    Discounts[Offset Threshold],
    0
)

VAR Result =
( [EV Monthly Sales] * AllClientDiscount ) * (1 - [Monthly Discount Lookup] )

VAR FiltResult =
IF(
    SELECTEDVALUE( Dates[FirstDayofMonth] ) <= SELECTEDVALUE( Data[Go Live Date] ),
    BLANK(),
    Result
)

VAR vTable =
CALCULATETABLE(
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES( Data[Account Name] ),
            VALUES( Dates[FirstDayofMonth] )
        ),
        "@DiscMnthlySales", [Discounted Monthly Sales]
    )
)

VAR vTabTotals =
SWITCH( TRUE(),
    // Matrix Cell Values (A)
    AND(
        HASONEVALUE( Dates[FirstDayofMonth] ),
        HASONEVALUE( Data[Account Name] )
        ), FiltResult,
    // Right Side (Row) Totals (B)
    HASONEVALUE( Data[Account Name] ),
        CALCULATE(
            SUMX(
                vTable,
                [@DiscMnthlySales]
            ),
           VALUES(Dates[FirstDayofMonth] )
        ),
    // Bottom (Column) Totals (C)
    HASONEVALUE( Dates[FirstDayofMonth] ),
        CALCULATE(
            SUMX(
                vTable,
                [@DiscMnthlySales]
            ),
           VALUES( Data[Account Name] )
        ),
    // Grand Total (D)
    CALCULATE(
            SUMX(
                vTable,
                [@DiscMnthlySales]
            )
    )
)

RETURN 
vTabTotals

Now that we’ve done the heavy lifting above, redoing the visual by stage simply becomes a matter of dragging and dropping the Stage Description field into the matrix rows well instead of Account Name, and boom!:

I hope this is helpful. Enjoyed working with you on this one – your definition of the problem and examples have been really clear and useful.

Full solution file attached below.

  • Brian

P.S. @Greg - I’m looping you in here since this is a pattern we may want to consider adding in the fixing totals DAX pattern section.

eDNA - monthly ramp-up solution2.pbix (115.0 KB)

1 Like

@BrianJ…AMAZING!!!

I am going to need to study this for a while. This is a piece of art. I think doing a module on this would be very helpful. There are so many complex calculations inside here.

Well Done!

@msedlak,

Thanks! Please give a shout if you have any questions as you work through the logic of this measure. It’s definitely worth taking the time to do top to bottom, since this measure has a number of really foundational virtual table manipulation concepts at the root of it. Once you’ve got the fundamentals of DAX down pretty well, developing capability with virtual tables is the next thing to focus on IMO (feels like gaining a DAX superpower…).

Excellent idea to do a separate module on this, since I can definitely see it being a recurring pattern that others can use in a variety of contexts . I’ll talk with @sam.mckay, and one of us definitely will take this up as a short video on the Enterprise DNA TV YouTube Channel. Appreciate the suggestion.

  • Brian