Cumulative Sum based on conditions involving user filter

[TableauIsBetter]

I have a table that looks something like this:

Date            Condition       Amount

01/01/2023     N                  10

01/01/2023      Y                   5

02/01/2023     No                11

.

.

.

and so on. The conditions are either Y/Yes or N/No.

I have a simple cumulative sum set up already:

Measure = CALCULATE(SUM('table'[Amount]), ISONORAFTER('table'[Date], MAX('table'[Date]), DESC)

This calculates the cumulative sum starting from the earliest date that the user filters for ('last 6 months', etc). The measure will be plotted as a graph with the filter available as a slicer.

But the trouble is that the cumulative sum I want is slightly more complicated than that:

Suppose the user filters for a start date of 02/01/2023. Then I want to include the rows with condition Yes (or Y) in the cumulative sum even before 02/01/2023 but not the rows with  condition No (or N). On the other hand, I want to include the rows with condition No (or N) after 02/01/2023.

In other words if the user filters for a start date of D. Then:

(a) Rows with condition Y or Yes before date D are included in cumulative sum

(b) Rows with condition Y or Yes after (or on) date D are included in cumulative sum

(c) Rows with condition N or No after (or on) date D are included in cumulative sum

(d) Rows with condition N or No before date D are not included in cumulative sum

In pseudocode the formula for such a thing would be:

CUMSUM(Amount * NOT (Condition = N or No and date <= User_filter_date))

(Here I'm considering the boolean value on the right side of the product as a 0/1 value so that it can be multiplied with amount which is an integer)

Finally I need this in such a way that the graph I mentioned above (date is x-axis, cumsum is y-axis) still starts from the user_filter_start_date rather than the very first date in the table (even though we might potentially include the very first row in the table in our measure calculations).

I am new to DAX and not sure how to implement this. Would appreciate some help.

[Jihwan_Kim]

Hi, I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result measure: = 
VAR _periodstart =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR _cumulatesumperiod =
    CALCULATE (
        SUM ( Data[Amount] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALLSELECTED ( 'Calendar'[Date] ),
            ORDERBY ( 'Calendar'[Date], ASC )
        ),
        REMOVEFILTERS ( 'Condition' )
    )
VAR _ALLsumbeforeperiodcondition =
    CALCULATE (
        SUM ( Data[Amount] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < _periodstart )
    )
RETURN
    _ALLsumbeforeperiodcondition + _cumulatesumperiod

[Anonymous]

Hi @TableauIsBetter,

For the cumulative calculation based on selected date, you can refer to the following sample formula:

formula =
VAR selected =
    MAX ( NewTable[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            NOT ( Table1[Condition] IN { "N", "No" } )
                && Table1[Date] <= selected
        )
    )

BTW, how did this table records mapping with calendar table date field? Since we not so clear for the user selection table structure, can you please share some dummy data that keep the raw data structure with expected results?

They should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[TableauIsBetter]

Thank you for your reply. 

The Date field in this table maintains an active relationship with the Calendar table's date. 

The user selection is from the slicer view of the graph I mentioned above (where they can filter on date)

Please let me know if you need more information - I'm new to powerbi so not entirely sure which information is relevant and which is not

[Anonymous]

Hi @TableauIsBetter ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng