Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |