How to get cumulative total function with criteria

[Maxakay]

Hey,

I'm trying to get a cumul without "dates" (no TOTALYTD for example).

How to, in DAX, calculate the cumul in yellow ?

I found this code but there is a criteria missing such as colomn "Fruits"

Cumulative_Goals = 
CALCULATE (
    SUM ( DATA[Sales Goals] ),
    ALL ( DATA ),
    DATA[Month] <= EARLIER ( DATA[Month])
)

Thank you for your help.

[Anonymous]

Hi @Maxakay ,

Please have a try.

Create a measure.

measure =
CALCULATE (
    SUM ( date[salesgoals] ),
    FILTER (
        ALL ( data ),
        data[month] <= SELECTEDVALUE ( data[month] )
            && data[fruit] = SELECTEDVALUE ( data[fruit] )
    )
)

Or a column.

column =
CALCULATE (
    SUM ( date[salesgoals] ),
    FILTER (
        data,
        data[month] <= EARLIER ( data[month] )
            && data[fruit] = EARLIER ( data[fruit] )
    )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Maxakay]

Hi,

Thanks for your help.

It works. However, is that possible to "blank" the sum when the "Sales Result" column hasn't information ?

Thanks a lot.

[Mihai_Iso]

Hi,

for multiple criteria

Cumulative Total with Criteria =

CALCULATE(
SUM(Table[Value]),
FILTER(
ALL(Table),
Table[Date] <= MAX(Table[Date])
&& Table[Column 1] = "Fruits"
&& (
CONTAINS(EARLIER(Table), Table[Column 2], "orange")
|| CONTAINS(EARLIER(Table), Table[Column 2], "apple")
|| CONTAINS(EARLIER(Table), Table[Column 2], "pears")
)
&& Table[Date] <= EARLIER(Table[Date])
)
)

[Maxakay]

Hi,

Thanks for the help.

I tried your formula :

It doesn't work at this point : "CONTAINS(EARLIER(Table),"

Table = DATA but i got this error : "The first argument of EARLIER/ERLIEST is not a valid column reference in the earlier row context."

I must miss something...

[Mihai_Iso]

Hi,

try this one:

-------------------------

Value = Sales Goal

Criteria=Fruits

Date=from a Calendar "Date"

---------------------------

Cumulative Total with Criteria = CALCULATE(
SUM(Table[Value]),
FILTER(
ALL(Table),
Table[Date] <= MAX(Table[Date])
&& Table[Criteria] = "Your Criteria Here"
)
)

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=Ebepc_6ULVA

[Maxakay]

Hi,

Thanks for the link. I tried but it doesn't work.

Cumulative Goals = 
VAR Fruits =
    RANKX(
        ALL(DATA[Fruits]),
        [TOTALSales_Goals],,
        DESC,
        Dense
    )
VAR CumulativeGoals =
    Calculate(
        [TOTALSales_Goals],
        Filter(
            ALL(DATA[Fruits]),
            Fruits >= RANKX(
                ALL(DATA[Fruits]),
                [TOTALSales_Goals],,
                DESC,
                Dense
            )
        )
    )
RETURN
CumulativeGoals

I can't join a .pbx file to illustrate the problem 😕