Projecting current profit run rate

DAX DAX Calculations
1

Hi Everyone,

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.

2

This is an interesting one.

I’ve sort of touched on this here but this maybe requires something that’s slightly adjusted here to compensate for the daily allocation

I’ll work on something here and add it to this post shortly.

3

Here’s a solution for you.

Two step.

First we want to work out the daily average run rate for the days that have been in any year

Like so

Avg. Daily Run Rate = 
VAR DaysWithSales = CALCULATE( COUNTROWS( Dates ), FILTER( ALLSELECTED( Dates ), [Total Sales] > 0 ) )
VAR CumulativeTotal = CALCULATE( [Cumulative Sales], ALLSELECTED( Dates ) )

RETURN
DIVIDE( CumulativeTotal, DaysWithSales, 0 )

image
image.png1266×654 38.5 KB

Then we can just calculate a cumulative total from this.

See below

Cumulative Run Rate = 
VAR AvgRate = [Avg. Daily Run Rate]

RETURN
CALCULATE( 
    SUMX( SUMMARIZE( Dates, Dates[Date], "RunRate", AvgRate ), [RunRate] ),
       FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Dates[Date] ) ) )

image
image.png1214×651 36.2 KB

See how this projects it forward.

It’s also dynamic so you can use this across different time frames.

See how you go with these ideas.

Thanks

4

Sam,

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!

Cumulative Performance Daily Allocation = 
VAR DaysWithActivity = CALCULATE( max(Date_Tbl[Day of Year]), FILTER( ALLSELECTED( Date_Tbl ), 'Metric Selection'[Selected Metric] > 0 ) )
VAR CumulativeTotal = CALCULATE( [Cumulative Performance CY], ALLSELECTED( Date_Tbl ) )

RETURN
DIVIDE( CumulativeTotal, DaysWithActivity, 0 )
5

That’s great. Chrs

6

Hey Sam @sam.mckay ,

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?

Cheers,

7

Hi Robert,

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

Avg. Daily Run Rate (Dynamic) = 
VAR DaysWithSales = CALCULATE( COUNTROWS( Dates ), FILTER( ALLSELECTED( Dates ), [Total Sales] > 0 ) )
VAR CumulativeTotal = CALCULATE( [Cumulative Sales], ALLSELECTED( Dates ) )

RETURN
DIVIDE( CumulativeTotal, DaysWithSales, 0 )

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

Thanks

8

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
image

9

Looks like I figured it out. It seems to be working fine now. I couldn’t have done this without your teachings. Thank you for all you do for us!

Cumulative Run Rate = 
VAR AvgRate = [Avg. Daily Run Rate]

RETURN
CALCULATE( 
SUMX( SUMMARIZE( DimDate, DimDate[DateKey], "RunRate", AvgRate ), [RunRate] ),
   FILTER( ALLSELECTED( DimDate ), DimDate[DateKey] <= MAX( DimDate[DateKey] ) ),
   FILTER(ALLSELECTED(DimDate) , DimDate[IsBusinessDay]=1 ))
10

Yep nice one, looks good.

That’s definitely the combination you need. Looks nice.