Forum Discussion

Avermaak's avatar
Avermaak
Icon for Helper I rankHelper I
4 years ago
Solved

DAX 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 firstly I created a new actual by overriding the 0.00 values with the budget but when I do the cumulative formula it does not add the amount as needed. Please assist with the cumulative amounts, below are the formula's used

 

NEW Actual =
IF (SUM('Key Metrics per Month'[Actual]) = BLANK (),sum('Key Metrics per Month'[Budget]),0) + SUM('Key Metrics per Month'[Actual])
 
Cumulative Actual =
CALCULATE(
    [NEW Actual],
    FILTER(
        ALLSELECTED('Key Metrics per Month'[Financial Period]),
        ISONORAFTER('Key Metrics per Month'[Financial Period], MAX('Key Metrics per Month'[Financial Period]), DESC)
    )
)

 

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

    DAX Formula HELP.pbix

     

    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

5 Replies

  • Anand24's avatar
    Anand24
    Icon for Super User rankSuper User
    4 years ago

    Hi 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 !!!
    LinkedIn

     

    • Avermaak's avatar
      Avermaak
      Icon for Helper I rankHelper I
      4 years ago

      Thank you for the response but it is giving me the following error

       

       

      • Anand24's avatar
        Anand24
        Icon for Super User rankSuper User
        4 years ago

        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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

    DAX Formula HELP.pbix

     

    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