Forum Discussion
Avermaak
Helper I
Helper IDAX Formula HELP
Good day, I need some assistance with a dax formula for Cumulative actuals. Due to me only having one actual for the first period, I want to override the 0.00 values with the budget amount. So f...
Hi Avermaak ,
Here are the steps you can follow：
1. Create measure.
Cumulative_Actual = SUMX( FILTER(ALLSELECTED('Key Metrics per Month'), 'Key Metrics per Month'[Financial Period]<=MAX('Key Metrics per Month'[Financial Period])),[NEW Actual])2. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anand24
Super User
Super UserHi Avermaak ,
Try below DAX for calculated measure:
Cumulative_Actuals_Measure =
Var Actual = IF (SUM('Key Metrics per Month'[Actual]) = BLANK (),sum('Key Metrics per Month'[Budget]),0) + SUM('Key Metrics per Month'[Actual])
Return
CALCULATE (
Actual,
FILTER ( ALL('Key Metrics per Month'), Actual >= MAX (Actual) ),
VALUES ( 'Key Metrics per Month'[Financial Period] )
)
Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! |
AvermaakThank you for the response but it is giving me the following error
- Anand24
Avermaak ,
Can you try the below:Cumulative_Actuals_Measure =
Var Actual = IF (SUM('Key Metrics per Month'[Actual]) = BLANK (),sum('Key Metrics per Month'[Budget]),0) + SUM('Key Metrics per Month'[Actual])
Return
CALCULATE(
Actual,
FILTER(
ALL(Key Metrics per Month),
'Key Metrics per Month'[Financial Period]<>MAX('Key Metrics per Month'[Financial Period])
)
)
Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!
Proud To Be a Super User !!!
LinkedIn