Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
Thank you!
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |