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

[Amar_Kumar]

On Hand Cumulative Total =
VAR CurrentMonth = MAX('DimDate'[Date])
RETURN
SUMX(
FILTER(
ALL('DimDate'[Date]),
'DimDate'[Date] <= CurrentMonth
),
CALCULATE(
COUNTX(
FILTER(
Table1,
Table1[ValidDate] < 'DimDate'[Date] &&
(ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= '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)
)
)

Please mark my answer as complete

[Libin7963]

Thanks for your reply, I am getting this error - A single value for column 'Date' in table 'DimDate' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Amar_Kumar]

@Libin7963.  Please try this

FILTER(
Table1,
Table1[ValidDate] < SELECTEDVALUE('DimDate'[Date]) &&
(ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= SELECTEDVALUE('DimDate'[Date])) &&
Table1[Type] IN {
"01", "02", "03", ..., "18"
}
)

[Libin7963]

Thanks, I updated it as below . On Hand Cumulative Total = VAR CurrentMonth = MAX('DimDate'[Date]) RETURN SUMX( FILTER( ALL('DimDate'[Date]), 'DimDate'[Date] <= CurrentMonth ), CALCULATE( COUNTX( FILTER( Table1, Table1[ValidDate] < SELECTEDVALUE('DimDate'[Date]) && (ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= SELECTEDVALUE('DimDate'[Date])) && Table1[Type] IN { "01", "02", "03", ..., "18" } ) , Table1[REFVAL] ), CROSSFILTER('DimDate'[Date], Table1[ValidDate], None) ) ) and getting this. 

[Amar_Kumar]

@Libin7963 please find below the optimized version.

On Hand Cumulative Total =

VAR CurrentDate = MAX('DimDate'[Date])

RETURN

    SUMX(

        FILTER(

            ALL('DimDate'[Date]),

            'DimDate'[Date] <= CurrentDate

        ),

        CALCULATE(

            COUNTROWS(

                FILTER(

                    Table1,

                    Table1[ValidDate] < 'DimDate'[Date]

                    && (ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= 'DimDate'[Date])

                    && Table1[Type] IN { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10",

                                         "11", "12", "13", "14", "15", "16", "17", "18" }

                )

            ),

            CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)

        )

    )

[Libin7963]

Thanks, I am getting this error  -

Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.

[Amar_Kumar]

Replace sunx section in the code, try passing 

SUMX(full table to filter.

    FILTER(

        ALL('DimDate'), 

        'DimDate'[Date] <= CurrentDate

    ),

[Libin7963]

modified as below and getting same error - 

n Hand Cumulative Total = VAR CurrentDate = MAX('DimDate'[Date]) RETURN SUMX(Table1,FILTER(ALL('DimDate'[Date]),'DimDate'[Date]....

[Amar_Kumar]

I tried and didnot got error with this modification, 

On Hand Cumulative Total =

VAR CurrentDate = MAX('DimDate'[Date])

RETURN

    SUMX(

        FILTER(

            ALL('DimDate'),

            'DimDate'[Date] <= CurrentDate

        ),

        CALCULATE(

            COUNTROWS(

                FILTER(

                    Table1,

                    Table1[ValidDate] < EARLIER('DimDate'[Date]) &&

                    (

                        ISBLANK(Table1[DecisionDate]) ||

                        Table1[DecisionDate] >= EARLIER('DimDate'[Date])

                    ) &&

                    Table1[Type] IN {

                        "01", "02", "03", "04", "05", "06", "07", "08", "09",

                        "10", "11", "12", "13", "14", "15", "16", "17", "18"

                    }

                )

            ),

            CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)

        )

    )

[Libin7963]

I am getting this. "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

Please note that I use a date slicer by month from date table and valid date is active relationship and decision issued is limited relationship to date table 

[Amar_Kumar]

@Libin7963  

Replaced earlier with variable loopdate 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", "03", "04", "05", "06", "07", "08", "09",
"10", "11", "12", "13", "14", "15", "16", "17", "18"
}
)
),
CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
)
)

[Libin7963]

getting this 

[Amar_Kumar]

On Hand Cumulative Total =

VAR CurrentDate = MAX('DimDate'[Date])

-- Pre-filtered table

VAR FilteredBase =

    FILTER(

        Table1,

        Table1[Type] IN {

            "01", "02", "03", "04", "05", "06", "07", "08", "09",

            "10", "11", "12", "13", "14", "15", "16", "17", "18"

        }

    )

RETURN

    SUMX(

        FILTER(

            ALL('DimDate'),

            'DimDate'[Date] <= CurrentDate

        ),

        VAR LoopDate = 'DimDate'[Date]

        RETURN

            CALCULATE(

                COUNTROWS(

                    FILTER(

                        FilteredBase,

                        FilteredBase[ValidDate] < LoopDate &&

                        (

                            ISBLANK(FilteredBase[DecisionDate]) ||

                            FilteredBase[DecisionDate] >= LoopDate

                        )

                    )

                ),

                CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)

            )

    )

[Libin7963]

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]

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.

[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.

[Libin7963]

Hi, sorry for late response. I got this error 

[gmsamborn]

Hi @Libin7963 

Based on your error message, I would make sure the column 'Table1'[REFVAL] is a numeric datatype.

[Libin7963]

It is text datatype which looks like 200/255/FNC . I am trying to find the count of rows and not total

[v-menakakota]

Hi @Libin7963 ,

Try to Replace the measure with Countx and check if it works.