Cumulative Total

[Libin7963]

Below Dax is used for calculating the request on hand at the beginning of the selected period. I would like to calculate the cumulative total (example :  Jan = 3, Feb = 2, March 4 then Jan = 3,  Feb = 5 and March =9)for selected period(by month) in a matrix table. Table 1 has an active relationship with Valid date and inactive relationship with Decision date. Any assistance appreciated.

On Hand Cumulative =
CALCULATE(
COUNTX(
FILTER(
Table1,
Table1[ValidDate] < MIN('DimDate'[Date]) &&
(ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= MIN('DimDate'[Date])) &&
Table1[Type] IN {
"01", "02", "03", "04", "05", "06", "07", "08",
"09", "10", "11", "12", "13", "14", "15", "16",
"17", "18"
}
),
Table1[REFVAL]
),
CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
)

[Libin7963]

No, that didn't work. My data is like this and it is the Case count cumulative is what I am not getting 

[v-menakakota · Accepted solution]

Hi @Libin7963 ,

Once try this:

On Hand Cumulative Total :=
VAR CurrentDate = MAX('DimDate'[Date])
RETURN
SUMX(
    FILTER(
        ALL('DimDate'),
        'DimDate'[Date] <= CurrentDate
    ),
    VAR LoopDate = 'DimDate'[Date]
    RETURN
        CALCULATE(
            COUNTROWS(
                FILTER(
                    Table1,
                    Table1[ValidDate] < LoopDate &&
                    (
                        ISBLANK(Table1[DecisionDate]) ||
                        Table1[DecisionDate] >= LoopDate
                    ) &&
                    Table1[Type] IN {
                        "01", "02", ..., "18"
                    }
                )
            ),
            CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
        )
)

[v-menakakota]

Hi @Libin7963 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.