Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am trying to forecast montly bookings based on last years allocation.
I have a table with the montly allocation of bookings for last year: I want to use this to forecast the upcoming months. First based on the actuals for the year, i need to calcuate the total for the year and then split this amount by month based on the allocations in the the table below.
For example: The actual for Jan is 180, i will use this to predict the total for the year by saying 180/0.011141175=16,156. Taking this total of 16,156 i will multiply it by last year booking % of total to get the monthly allocation.
Next given the actual for Jan(180) + Feb (90) =270, i will use this to predict the total for the year by saying 180+90/(0.011141175+0.012732771)=11,309. Taking this total of 11,309 i will multiply it by last year booking % of total to get the monthly allocation.
Month | Last Years bookings | last year booking % of total |
Jan | 140 | 0.011141175 |
Feb | 160 | 0.012732771 |
Mar | 200 | 0.015915964 |
Apr | 450 | 0.035810918 |
May | 700 | 0.055705873 |
Jun | 900 | 0.071621837 |
Jul | 1,500 | 0.119369728 |
Aug | 2,500 | 0.198949546 |
Sep | 1,000 | 0.079579819 |
Oct | 1,500 | 0.119369728 |
Nov | 500 | 0.039789909 |
Dec | 3,016 | 0.240012733 |
Total | 12,566 |
Hi @ninakarsa ,
The words description is not easy to explain clearly, why not display the final result you want in a table? You can do it in excel.
Best regards,
Lionel Chen
@ninakarsa based on what you asked I have assumed some data for the current year actuals as following and added an index to the base table using M
The source table looks like this now
Month | Last Years bookings | last year booking % of total | Assumed Current Year Actual | Index |
Jan | 140 | 0.011141 | 180 | 1 |
Feb | 160 | 0.012733 | 90 | 2 |
Mar | 200 | 0.015916 | 300 | 3 |
Apr | 450 | 0.035811 | 85 | 4 |
May | 700 | 0.055706 | 65 | 5 |
Jun | 900 | 0.071622 | 280 | 6 |
Jul | 1500 | 0.11937 | 800 | 7 |
Aug | 2500 | 0.19895 | 354 | 8 |
Sep | 1000 | 0.07958 | 725 | 9 |
Oct | 1500 | 0.11937 | 213 | 10 |
Nov | 500 | 0.03979 | 89 | 11 |
Dec | 3016 | 0.240013 | 56 | 12 |
Now this is a running total problem to me and I have solved it in this way
The measures are following
Assumed Current year total sumx:= SUMX('Table','Table'[Assumed Current Year Actual])
Assumed Current year running total:=
VAR _1 = MAX('Table'[Index])
VAR _2 = CALCULATE(CALCULATE([Assumed Current year total sumx],ALL('Table'[Index]),'Table'[Index]<=_1),ALL('Table'[Month]))
RETURN _2
last year booking % sumx:= SUMX('Table','Table'[last year booking % of total])
Last year booking % running total:=
VAR _1 = MAX('Table'[Index])
VAR _2 = CALCULATE(CALCULATE([last year booking % sumx],ALL('Table'[Index]),'Table'[Index]<=_1),ALL('Table'[Month]))
RETURN _2
thanks for your response.
so what I am trying to do is calculate the forecast for 2020 based on the % monthly proportion of 2019.
For example, last year January consiting had a monthly proportion of total bookings of 0.011141175.
So going into this year if the actual bookings for Jan 2020 are 100 then if we say 100/0.011141175 this will give us the total forecast for 2020, and then each time the month closes to repeat the same proces. For example say February finished at 180, then to forecast end of year this would be (actual jan +actual feb)= 100+180 / (last year monthly proportion of total bookings)
@ninakarsa my calculation exactly does that
YTD Forecast = YTD Actual/ YTD Last Year's Percentage of Grand Total
how do I calculate last years monthly % of yearly total?
thanks
last year % of total = divide(CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))),
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(endofyear(dateadd('Date'[Date],-12,MONTH)))))
Above is last year month and below is year using Datesmtd and datesytd
Looking at values it not clear when you taking this year Jan. can explain by putting final(Expected) values.
But to achieve this you need date dimension and following formulas.
last year % of total =
divide(CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))),
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))))
last year ytd = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-12,MONTH)))
this year ytd CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(Date'[Date]))
this year mtd =CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -Decoding Direct Query - Time Intelligence , Winner Coloring on MAP , >HR Analytics , Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
so what I am trying to do is calculate the forecast for 2020 based on the % monthly proportion of 2019.
For example, last year January consiting had a monthly proportion of total bookings of 0.011141175.
So going into this year if the actual bookings for Jan 2020 are 100 then if we say 100/0.011141175 this will give us the total forecast for 2020, and then each time the month closes to repeat the same proces. For example say February finished at 180, then to forecast end of year this would be (actual jan +actual feb)= 100+180 / (last year monthly proportion of total bookings)
thanking you in advance
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |