Forum Discussion

Libin7963's avatar
Libin7963
Icon for Helper II rankHelper 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)
            )
    )

Libin7963's avatar
Libin7963
Icon for Helper II rankHelper II
1 year ago

Below table shows the on hand non cumulative for my visual 

but the cumulative visual is showing below numbers. I think it is not filtering properly

 

 

v-menakakota's avatar
v-menakakota
Icon for Community Support rankCommunity Support
1 year ago

Hi Libin7963 ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.


Can you try this Dax:

On Hand Cumulative Total =
CALCULATE(
    CALCULATE(
        sumx(
            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)
    ),
    FILTER(
        ALL('DimDate'),
        'DimDate'[Date] <= MAX('DimDate'[Date])
    )
)

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.