topic Re: Cumulative Total in DAX Commands and Tips

Cumulative Total

Libin7963 — Tue, 22 Apr 2025 15:31:01 GMT

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

Re: Cumulative Total

Amar_Kumar — Tue, 22 Apr 2025 17:47:06 GMT

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

Re: Cumulative Total

Libin7963 — Wed, 23 Apr 2025 09:39:40 GMT

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.

Re: Cumulative Total

Amar_Kumar — Wed, 23 Apr 2025 11:22:21 GMT

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

Re: Cumulative Total

Libin7963 — Thu, 24 Apr 2025 09:14:05 GMT

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.

Re: Cumulative Total

Amar_Kumar — Thu, 24 Apr 2025 09:20:09 GMT

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)

)

Re: Cumulative Total

Libin7963 — Thu, 24 Apr 2025 09:27:52 GMT

Thanks, I am getting this error -

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

Re: Cumulative Total

Amar_Kumar — Thu, 24 Apr 2025 09:33:28 GMT

Replace sunx section in the code, try passing

SUMX(full table to filter.

FILTER(

ALL('DimDate'),

'DimDate'[Date] <= CurrentDate

Re: Cumulative Total

Libin7963 — Thu, 24 Apr 2025 09:43:41 GMT

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

Re: Cumulative Total

Amar_Kumar — Thu, 24 Apr 2025 09:46:04 GMT

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)

)

Re: Cumulative Total

Libin7963 — Thu, 24 Apr 2025 09:51:07 GMT

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

Re: Cumulative Total

Amar_Kumar — Thu, 24 Apr 2025 09:55:44 GMT

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

Re: Cumulative Total

Libin7963 — Thu, 24 Apr 2025 10:02:47 GMT

getting this

Re: Cumulative Total

Amar_Kumar — Thu, 24 Apr 2025 10:06:15 GMT

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)

)

Re: Cumulative Total

Libin7963 — Thu, 24 Apr 2025 10:22:06 GMT

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

Re: Cumulative Total

v-menakakota — Tue, 29 Apr 2025 11:05:10 GMT

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.

Re: Cumulative Total

v-menakakota — Fri, 02 May 2025 09:12:22 GMT

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.

Re: Cumulative Total

Libin7963 — Fri, 02 May 2025 21:17:39 GMT

Hi, sorry for late response. I got this error

Re: Cumulative Total

gmsamborn — Sat, 03 May 2025 01:54:48 GMT

Hi Libin7963

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

Re: Cumulative Total

Libin7963 — Sat, 03 May 2025 15:01:28 GMT

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