I am trying to figure out how I can take my cumulative profit at any given time in the year and project that run rate over the remainder of the year. I can get to where I can take my current cumulative profit and generate a daily allocation. However, I can not seem to be able to project that daily allocation and do a new cumulative total on my run rate for the rest of the year. The end game is to take an area chart and display my cumulative profit during any point during the year and then also display my projected run rate for the remainder of the year. I hope this makes sense what I am trying to accomplish. It is very similar to your forecasting tutorials, but except using last year as my forecast, I want to use my current cumulative run rate to see how I will finish the year. Thanks.
Thanks so much!! I forgot the use of summarize. The only thing I had to change is that I had to divide by the “day of year” rather than the number of days that had activity. Otherwise, I am using this daily average number assuming that every day for the remainder of the year will have activity. If I use the count of days based on only activity I will greatly overstate my run rate projecting it over the remainder of the year. This worked beautifully!
Just watched your youtube video you made on this. I was wondering if you could help extend this concept further for me.
I have implemented exactly as you have in the video however, this is limited to creating an average run rate for the selected slicer years - if i select 2017 (avg of 2017) if i select 2017 & 2018 (avg of both years). having more years selected seems to be a more accurate representation/ effect i am after. However what i need to adjust is the cumulative running total formula to either restart at the start of each FY or even if we have to have various formulas for each year only?
The formula as is is dynamic, in that it will calculate the total cumulative sales over any period and then divide that by the number of days where there were sales
The one thing with this formula is that the numbers are not static. As each new day comes in the run rate would adjust. (this may or may not be what’s needed )
Here’s video with a different technique for doing that, which just average up historic years. Is this helpful
You could adjust this for financial years.
Let me know about this or if you are looking for something different
Sam,
My sales happen on weekdays only as we are always closed on the weekends. In the formula, when projecting the rest of the year, it is taking the “Run Rate” and adding that total to every single date left in the calendar year, instead of just the weekdays. it is also adding the Run Rate to every single YTD date so my numbers are incorrect YTD and incorrect projecting forward. How do I modify the “Cumulative Run Rate” to just count the weekdays and not the weekends or Holidays? In my date table, I have a column named “IsWorkDay”, which has a 1 or 0. 1 = Working Day, and 0 = Weekend or Holiday