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.
I have the following columns and would like to create a measure which counts the days between payment and invoice date. At the end i would like to know which debtor pays on time and which don't.
Date | Invoice number | Journalcode | |
1-3-2018 | 123456789 | 25 | (Payment) |
15-4-2018 | 123456789 | 70 | (Invoice) |
Solved! Go to Solution.
Hi @Oomsen
Add a measure to get the amount as below. Here is the sample file.
Amount = VAR a = SUMMARIZE ( Table1, Table1[Journalcode], Table1[Invoice number], "b", [Diff] ) RETURN SUMX ( a, [b] )
Regards,
Cherie
Hi @Oomsen
You may refer to below measure:
Measure = DATEDIFF ( CALCULATE ( MAX ( Table1[Date] ), FILTER ( ALL ( Table1 ), Table1[Date] < MAX ( Table1[Date] ) ) ), MAX ( Table1[Date] ), DAY )
Regards,
Cherie
I think it is working, thanks for that. But it doesn't give the result i expected. There are also transactions in the column which doesn't need to be count. I'm only looking for sales invoices and payments which can be based on journalcode 25 (payment) and 70 (invoice).
Hi @Oomsen
Could you provide more details for your scenario so that i could help further on it?
Regards,
Cherie
For sure. My table is called transactions and hold all financial transactions of our current administration. The table includes data like Date, Amount, Client/Supplier, GL account, etc. My goal is to find out that my customers are paying avrage on the 30 days that we have agreed. My idea wass to measure this bij counting the days between payment and invoice based on journalcode.
Hi @Oomsen
I add some data in the sample. You may refer to it and check if it matches what you need.
Measure2 = VAR a = IF ( MAX ( Table1[Journalcode] ) = 70, CALCULATE ( MAX ( Table1[Date] ), FILTER ( ALLEXCEPT ( 'Table1', Table1[Invoice number] ), Table1[Journalcode] = 25 ) ) ) RETURN DATEDIFF ( a, MAX ( Table1[Date] ), DAY )
Regards,
Cherie
I think this is not correct, the results are like -982. i also need notice that the transaction on journalcode 25 will always be later in time then 70.
Hi @Oomsen
Could you explain how to get the results like -982? I think i'm not fully understand it. It's better you can give some sample data and how to get the expected results.
Regards,
Cherie
That was the result of the presented formule up here.
Below some expamle data of one invoice and payment. the table includes a lot of these invoices and other transactions. That's why i had the idea to select bij journalcode. Journalcode 70 is the invoice and journalcode 25 is the payment. I want to calculate the amount of days between invoice and payment.
Hi @Oomsen
Add a measure to get the amount as below. Here is the sample file.
Amount = VAR a = SUMMARIZE ( Table1, Table1[Journalcode], Table1[Invoice number], "b", [Diff] ) RETURN SUMX ( a, [b] )
Regards,
Cherie
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 |