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 a...
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()
    )

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 🙂