topic DAX equivalent for SUMIFS with current row value as filter in DAX Commands and Tips

DAX equivalent for SUMIFS with current row value as filter

Akbarov — Tue, 26 Jul 2022 07:49:36 GMT

Hello Dear community,

I have column of sales,

[Quantity] [Client name] [Date]

I need DAX function to sum quantity

SUM [Quantity]

IF [Date] < Current row

and Client name = Current row

It could be something like this in Excel:

Sumifs(Quantity,[Client name],A10,[Date],"<"&C10,..)

Can anybodyhelp me please to solve this problem?

Re: DAX equivalent for SUMIFS with current row value as filter

amitchandak — Tue, 26 Jul 2022 07:57:55 GMT

Akbarov ,

A new column = sumx(filter(Table, [Client] =earlier([Client]) && [Date] < earlier([Date]) ), [Quantity])

A new Measure = sumx(filter(allselected(Table), [Client] =max([Client]) && [Date] < max([Date]) ), [Quantity])

Re: DAX equivalent for SUMIFS with current row value as filter

tamerj1 — Tue, 26 Jul 2022 08:26:45 GMT

Hi Akbarov

for a calculated column please use

= VAR CurrentDate = TableName[Date] RETURN CALCULATE ( SUM ( TableName[Quantity] ), TableName[Date] < CurrentDate, ALLEXCEPT ( TableName, TableName[Client name] ) )

For measure use

= CALCULATE ( SUM ( TableName[Quantity] ), TableName[Date] < MAX ( TableName[Date] ) )

Re: DAX equivalent for SUMIFS with current row value as filter

Akbarov — Tue, 26 Jul 2022 08:48:27 GMT

Thank you very much for fast reply.

I tried your solution. But my full function is like this

RELATED('(Table 1) Month'[Days of Month])*('(Table 3) Census'[Endorsement of Month]+

sumx(filter('(Table 3) Census', [Client] =earlier([Client]) && '(Table 3) Census'[Month] < earlier('(Table 3) Census'[Month]) ), '(Table 3) Census'[Active]))

I think result was not correct. May be because your function does not works with my function together?

I don't get any error. But can you tell me if full function is fine?

Re: DAX equivalent for SUMIFS with current row value as filter

Nacci11 — Tue, 20 Feb 2024 17:49:18 GMT

Hi tamerj1, I don't understand how this could work as the date will always be inferior to current date hence your formula seems like it would always give the sum of the entire Quantity column. Could you explain ?

Re: DAX equivalent for SUMIFS with current row value as filter

tamerj1 — Mon, 26 Feb 2024 16:57:46 GMT

Hi Nacci11

CurrentDate is tye Date of the current row under iteration.

CALCULATETABLE- ALLEXCEPT will produce a table that represent all the rows of the current client. This table is filtered for every row in the original table for ClientTable.Date < OriginalTable.Date