Cumulative Forecast

Question

Hi there,&nbsp;I am trying to add a cumulative forecast based on a 1 month rolling average of sales, to create a more accurate representation of the outlook for the next few months.&nbsp;&nbsp;I have created a 1 month average daily run rate using the following DAX:&nbsp;1 Month Average Daily Run Rate = AVERAGEX(DATESINPERIOD('Date Table'[Date],TODAY(),-1,MONTH),[Selected Measure Value]) where "Selected Measure Value" is Sales Revenue.&nbsp;I then created a cumulative total of this average using the following DAX:&nbsp;&nbsp;Cumulative Daily Run Rate = VAR AvgRate = [1 Month Average Daily Run Rate]&nbsp;RETURN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SUMX( SUMMARIZE('Date Table','Date Table'[Date], "RunRate", AvgRate), [RunRate]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER(ALLSELECTED('Date Table'), 'Date Table'[Date] &lt;= MAX('Date Table'[Date])))&nbsp;I then added my YTD Cumulative Revenue measure, my LY Cumulative Revenue measure and my Cumulative Daily Run Rate measure to a line chart, as seen below.&nbsp;My question is; can anyone tell me how i can design this so that the orange line (Cumulative daily run rate) starts where my YTD Cumulative Revenue (Dark Blue) ends, joining the two up. I want it so that where my YTD Cumulative Revenue ends, my 1 month average daily run rate gets added on to that YTD value, cumulatively, see below for table.&nbsp;&nbsp;&nbsp;The table looks like this (numbers have been changed for confidentiality). As of today (5/5/20), we have sales yesterday but today onwards we do not, hence no revenue on the 5th and 6th May. RR = run rate / **bleep** = cumulative / Rev = Revenue / m = month&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Revenue&nbsp; &nbsp; &nbsp; YTD **bleep**. Rev.&nbsp; &nbsp; &nbsp; 1mAvgDayRR&nbsp; &nbsp; &nbsp; &nbsp;CumRR4/5/20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3,000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2,2005/5/20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2,2226/5/20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2,244&nbsp;As you can see from the table, my YTD cumulative revenue stops at 3,000 as there are no more sales after the 4th May, but i want to then add the 1mAvgDayRR (1 Month Average Daily Run Rate) value onto that so that on the 5/5/20 i get 3,022, obviously this would have to be done with another measure and hence add another column, that is fine.&nbsp;I hope someone can help because i am sure this can be done someway!

v-zhenbw-msft · Accepted Answer

Hi&nbsp;BUK2020&nbsp;,&nbsp;We can use the IF function to meet your requirement.&nbsp;Measure = 
IF(
    MAX('date table'[Date])&lt;=TODAY(),
[YTD Cumulative Revenue],[Cumulative Daily Run Rate])&nbsp;The result like this,&nbsp;&nbsp;If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?&nbsp;Best regards,&nbsp;Community Support Team _ zhenbwIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Cumulative Forecast

2 Replies

Related content

Cumulative Actual and Forecast

Cumulative inventory Forecast

Cumulative Total

Forecasting with DAX

Cumulative Forecast