Sum Rows data

[Sophiapan]

Hi everyone,

I really need your help to solve this. 😫 This is how my data look like:

I wish to sum each Username's Balance Amount that earlier than or equal to the Transaction Date. If using excel it would be look like this =SUMIFS(C:C,A:A,A2,B:B,"<="&B2)

I wish to find a dax formula that could replace this excel formula.

Thank you in advance!

[amitchandak · Accepted solution]

@Sophiapan , Not very clear

Try a new column like

new column = Sumx(filter(Table, [Username] =earlier([username]) && [Transaction Date] <earlier([Transaction Date])) , [New amount])

or use <=

new column = Sumx(filter(Table, [Username] =earlier([username]) && [Transaction Date] <=earlier([Transaction Date])) , [New amount])

if this does not help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[bcdobbs · Accepted solution]

Try:

Measure Name =
VAR SelectedDate = SELECTEDVALUE (Table[Transaction Date] )

RETURN

CALCULATE (

    SUM (Table[Net Amount]),
    Table[Transaction Date] <= SelectedDate

)

You can then use it in a table visual with username and transaction date.

If you want a calculated column in the table it's self let me know.

[amitchandak · Accepted solution]

@Sophiapan , Not very clear

Try a new column like

new column = Sumx(filter(Table, [Username] =earlier([username]) && [Transaction Date] <earlier([Transaction Date])) , [New amount])

or use <=

new column = Sumx(filter(Table, [Username] =earlier([username]) && [Transaction Date] <=earlier([Transaction Date])) , [New amount])

if this does not help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.