Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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.
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.
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
Works perfectly. Thank you!!!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |