DAX equivalent for SUMIFS with current row value as filter

[Akbarov]

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?

[tamerj1]

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

[Nacci11]

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 ? 

[tamerj1]

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

[amitchandak]

@Akbarov ,

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

or

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

[Akbarov · Accepted solution]

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?