Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ninakarsa
Helper II
Helper II

forecast based in last years proportion

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.

 

MonthLast Years bookingslast year booking % of total
Jan                               1400.011141175
Feb                               1600.012732771
Mar                               2000.015915964
Apr                               4500.035810918
May                               7000.055705873
Jun                               9000.071621837
Jul                             1,5000.119369728
Aug                             2,5000.198949546
Sep                             1,0000.079579819
Oct                             1,5000.119369728
Nov                               5000.039789909
Dec                             3,0160.240012733
Total                           12,566 
8 REPLIES 8
v-lionel-msft
Community Support
Community Support

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

smpa01
Super User
Super User

@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

MonthLast Years bookingslast year booking % of totalAssumed Current Year ActualIndex
Jan1400.0111411801
Feb1600.012733902
Mar2000.0159163003
Apr4500.035811854
May7000.055706655
Jun9000.0716222806
Jul15000.119378007
Aug25000.198953548
Sep10000.079587259
Oct15000.1193721310
Nov5000.039798911
Dec30160.2400135612

Now this is a running total problem to me and I have solved it in this way

resx.PNG

 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

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
ninakarsa
Helper II
Helper II

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

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.