Forum Discussion

vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful Individual
1 year ago
Solved

Dax Logic Help for cumulative on Card

 

problem statement: my actuals. measure in the table shows cumulative value, but when i place the same measure on the card, i get that individual value of the month, as you can see in Feb its 50M as cumulative value and in card it shows as 32M

 

this is my measure for cumulative value

Actual. =
IF([Actuals]>0,
CALCULATE(
    [Actuals],
    FILTER(
        ALLSELECTED('GroupOPEXCAPEX'),
        'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
        'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
    )),BLANK()
)

 

this is my measure for month on month Actuals

Actuals =
IF(
    ISBLANK(
        CALCULATE(SUM('GroupOPEXCAPEX'[Value]), 'GroupOPEXCAPEX'[Actual/Budget] = "Actuals")
    ),
    0,  // Return 0 if blank
    CALCULATE(SUM('GroupOPEXCAPEX'[Value]), 'GroupOPEXCAPEX'[Actual/Budget] = "Actuals")  // Otherwise, return the sum
)

 

Need Help
Show and Tell
  • AnkitaaMishra's avatar
    AnkitaaMishra
    1 year ago

    Hi vjnvinod , I tried to replicate your problem statement and below formula is working for me. 

    Just change the ALLSELECTED to ALL in your case.

    IF([Actuals]>0,
    CALCULATE(
        [Actuals],
            ALL('GroupOPEXCAPEX'),
            'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
            'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
        ),BLANK()
    )

7 Replies

  • anmolmalviya05's avatar
    anmolmalviya05
    Icon for Super User rankSuper User
    1 year ago

    Hi vjnvinod, Please try this:

    Total Cumulative Value =
    CALCULATE(
    SUMX(
    ALLSELECTED('GroupOPEXCAPEX'[DateColumn]),
    [Actuals]
    ),
    'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
    )

  • AnkitaaMishra's avatar
    AnkitaaMishra
    Icon for Super User rankSuper User
    1 year ago

    Hi vjnvinod , I tried to replicate your problem statement and below formula is working for me. 

    Just change the ALLSELECTED to ALL in your case.

    IF([Actuals]>0,
    CALCULATE(
        [Actuals],
            ALL('GroupOPEXCAPEX'),
            'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
            'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
        ),BLANK()
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, vjnvinod 

     

    As AnkitaaMishra  said, you can try the following formula.

    Actual. = 
IF([Actuals]>0,
 CALCULATE([Actuals],
    FILTER( ALL('GroupOPEXCAPEX'),
        'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
        'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
    )),BLANK()
)

    Is this the result you expected?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • govind_021's avatar
    govind_021
    Icon for Super User rankSuper User
    1 year ago

    Hey , vjnvinod please try this

    Actuals Combined =
    IF(
    ISINSCOPE('GroupOPEXCAPEX'[DateColumn]),
    -- Cumulative logic for table rows
    IF(
    [Actuals] > 0,
    CALCULATE(
    [Actuals],
    FILTER(
    ALLSELECTED('GroupOPEXCAPEX'),
    'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
    'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
    )
    ),
    BLANK()
    ),
    -- Monthly value for card or other non-date visuals
    CALCULATE(
    SUM('GroupOPEXCAPEX'[Value]),
    'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
    )
    )

    • vjnvinod's avatar
      vjnvinod
      Icon for Impactful Individual rankImpactful Individual
      1 year ago

      govind_021  its returning me month on month values, not cumulative on the card

       

       

  • Sergii24's avatar
    Sergii24
    Icon for Super User rankSuper User
    1 year ago

    Hi vjnvinod, I believe that the complexity of your case comes from the use of ALLSELECTED as a table function

    Actual. =
    IF([Actuals]>0,
    CALCULATE(
    [Actuals],
    FILTER(
    ALLSELECTED('GroupOPEXCAPEX'), //<------THE ISSUE IS HERE
    'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&
    'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
    )),BLANK()
    )
     
    In this video, SQLBI team explains why it's tricky and provides a good guildelines on how to use it - https://youtu.be/gD8yLTtyW1k?si=MMi05sR9PjfBV78u.
     
    You don't need ALLSELECTED() to get a cummulative totals. Simply get the current max date as variable and then use it in Calculate():

    Actual. =
    VAR _MaxDate = MAX ('DateTable'[Date]) //MAKE SURE TO HAVE A PROPER DATE TABLE IN YOUR MODEL
    RETURN
    IF(
    [Actuals] > 0,
    CALCULATE(
    [Actuals],
    'DateTable'[Date]<= _MaxDate,
    'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
    ),
    BLANK()
    )


    This should help 🙂