Dax Logic Help for cumulative on Card

[vjnvinod]

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

)

[v-zhangtin-msft]

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.

[AnkitaaMishra · Accepted solution]

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]

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 🙂

[anmolmalviya05]

Hi @vjnvinod, Please try this:

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

[vjnvinod]

@anmolmalviya05 

it doesn't work either

[govind_021]

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]

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