Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |