Forecasting using Scenarios

[Stharwani]

HI team, 

I have a Data Model that includes Sales by End of Month and an Assumptions table with increase to Sales for following months. 

How can I apply the assumption to Actuals to forecast the sales.

Account	EOM	Amt	Ver
Sales	31/07/2017 0:00	$200,000.00	Act
Sales	31/08/2017 0:00	$245,700.00	Act
Sales	30/09/2017 0:00	$268,000.00	Act

Account	Forecast Start	Increase	Ver
Sales	31/10/2017 0:00	0.07	F1

Thanks for help.  

[Anonymous · Accepted solution]

@Stharwani,

Firstly, create a new table using the DAX below.

TestTable = UNION(SELECTCOLUMNS(Actual,"Date",Actual[EOM],"salesamount",Actual[Amt],"increase",BLANK()),SELECTCOLUMNS(Forecast,"Date",Forecast[Forecast Start],"salesamount",BLANK(),"increase",Forecast[Increase]))

Secondly, create a date table using the DAX below. Then create realtionship between date table and new table using date field.

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

At last, create the following measures in the new table.

Sales = SUM(TestTable[salesamount])

Last Sales = 
IF (
    ISBLANK (
        CALCULATE (
            [Sales],
            DATEADD ( 'Date'[Date], 1, MONTH )
        )
    ),
    [Sales],
    1
)

MultiplyBy = 
IF (
    ISBLANK ( [Last Sales] ),
    1 + MAX(TestTable[increase]),
    [Last Sales]
)

Cumulated LN = 
CALCULATE (
    SUMX ( VALUES ( 'Date'[Monthnumber] ), LN ( [MultiplyBy] ) ),
    DATESBETWEEN (
        'Date'[Date],
        BLANK (),
        MAX ( 'Date'[Date] )
    )
)

Sales ForeCast = 
SUMX (
    VALUES ( 'Date'[Monthnumber] ),
    IF ( ISBLANK ( [Sales] ), EXP ( [Cumulated LN] ), [Sales] )
)

Reference:
http://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/

Regards,
Lydia