Forum Discussion

Libin7963's avatar
Libin7963
Helper II
1 year ago
Solved

Cumulative Total

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 ...
Need Help
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    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)
            )
    )