Forum Discussion

Stharwani's avatar
Stharwani
Advocate II
8 years ago
Solved

Forecasting using Scenarios

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.

 

AccountEOMAmtVer
Sales31/07/2017 0:00$200,000.00Act
Sales31/08/2017 0:00$245,700.00Act
Sales30/09/2017 0:00$268,000.00Act

 

 

AccountForecast StartIncreaseVer
Sales31/10/2017 0:000.07F1
    

 

Thanks for help.  

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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