Future cumulative sum of estimated value

[michael163]

Hi,

I would welcome help on constructing this measure which I am stuck at. As of the most recent month I calculate a 6 month rolling average and this very same value I use as an estimate in the future months. 

The second part is that I need to do a cumulative sum from the moment that this estimate starts to work. I attach the example in excel screenshot.

I got to the point where I have the forumula for extending the 6-month average to the future:

Direct cash flow (subcontractors + estimate) =
VAR datum = [Last accounting date]
VAR estimate = CALCULATE( CALCULATE( [Direct cash flow (subcontractors)], DATESBETWEEN( 'Date'[Date], EOMONTH( MAX( 'Date'[Period]),-5), MAX( 'Date'[Period]))), 'Date'[Period] = datum, ALLSELECTED( 'Date'))
VAR Dates = CALCULATE (
CALCULATE ( COUNTROWS ( VALUES ( 'Date'[Period] ) ), 'Date'[Period] ),
DATESBETWEEN (
'Date'[Date],
EOMONTH( MAX( 'Date'[Period]), -5),
MAX( 'Date'[Period])
), 'Date'[Year] > 2018
)

Return [Direct cash flow (subcontractors)] + IF( MAX( 'Date'[Period]) <= [Last accounting date], BLANK(), estimate / Dates )

The problem is that when I do the cumulative sum the result doesn't do cumulative addition. It just shows the monthly values of the estimate

My formula for that is here:

FutureCumSum = CALCULATE( [Direct cash flow (subcontractors + estimate)], DATESBETWEEN( 'Date'[Date], EOMONTH( [Last accounting date], 1), MAX( 'Date'[Period])))

Thank you!

[MFelix]

Hi @michael163 ,

Since you are using a formula with if values that return blanks believe that the context is having issues on this, you need to use a SUMX or similar to get this done.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.