Summarize Table

[ThomasM]

Hi All

I am trying to summarize a table that contains payment of customers.

I want the first transaction date and first amount, also the last transaction date and last amount.

As soon as i have this information, i will be able to calculate what the customer should have paid me from when they started paying to now. 

In addition, i will know those who are behind.

PS: I am unable to get the FirstPaymentAmount and LastPaymentAmount. Any solution?

DAX Code:

BI_ARREARS = SUMMARIZE(BI_TRANSACTIONS, BI_TRANSACTIONS[Rim_No], "FirstTransactionDate", MIN(BI_TRANSACTIONS[Payment_Date].[Date]),"Total Amount Paid",SUM(BI_TRANSACTIONS[Payment_Amount]), "LastTransactionDate", MAX(BI_TRANSACTIONS[Payment_Date]))

 Results

[Greg_Deckler]

I do not have your original data or a sample of your data, but why are you using SUMMARIZE? I would think that if you had a customer table and it was related to your transaction table that you could just create 4 columns in this table:

First Date = MIN(Table[Date])

Last Date = MAX(Table[Date])

First Amount = CALCULATE(SUM(Table[Amount]),FILTER(Table,Table[Date]=MIN(Table[Date])))

Last Amount = CALCULATE(SUM(Table[Amount]),FILTER(Table,Table[Date]=MAX(Table[Date])))

I may not have the DAX 100% correct, you may need to use RELATEDTABLE or RELATED, but that's the general gist of it. 

[ThomasM]

Please see below table sample.

The reason i created a summary table is because the original table contains multiple payments for each customer and therefore i only require a single line that contains id, first date, first amount and last date, last amount. Once i have this, i can figure out whether the customer is behind with payments or not, that means i am create more columns on the summary table.

[v-huizhn-msft · Accepted solution]

Hi @ThomasM,

Please add another two <groupBy_columnName> arguments in summarize function. Please try the following formula. I test it using your given sample data and get expected result.

Table = SUMMARIZE(Table21,Table21[Rim_No],"FirstTransactionDate",MIN(Table21[Payment_Date]),"Total Amont Paid",SUM(Table21[Payment_Amont]),"LastTransactionDate",MAX(Table21[Payment_Date]),"first-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MIN(Table21[Payment_Date])),Table21[Payment_Amont]),"last-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MAX(Table21[Payment_Date])),Table21[Payment_Amont]))

If you have any question, please feel free to ask.

Best Regards,
Angelia

[ThomasM]

Works perfectly. Thank you!!!