Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone
First of all I'd like to mention, that I am rather new to PowerBI and I am reading a LOT (!) in this forum. The posts are very helpful and solved already numerous issues I faced. However, there is one problem I could not solve yet:
We are creating a accounts receivable dashboard for our sales employees, where they should see the open and due invoices of their customers. This is what it looks like (recreated example with sample data - due to confidentiality of the data):
The Open Invoices Table shows all unpaid Invoices. The Due Invoices Table shows all open invoices with a due date which is in the past.
The problem:
Our customers are allowed to make partial payments of an invoice. Those cases appear as negative numbers in the table and do not have a due date. This is a problem, as my sum for the due invoices is not correct.
My goal:
I wanted to create a measure which summarizes the "Balance in Transaction Currency" based on the "Customer Invoice ID". This is what I wrote as a measure:
Net Value per Invoice =
SUMX (
SUMMARIZE (
'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID],
"Net Balance", SUM('Accounts Receivable'[Balance in Transaction Currency])
),
[Net Balance]
)
Unfortunately this does not solve my issue. However, if I only use the Summarize function and create a new table with it, it works fine. But I want to avoid an additional Table in our dataset, as it is already pretty big and hard to see all the relationships.
Sample data:
The table is called "Accounts Receivable" in our data model.
Business Partner ID (Account ID) | Customer Invoice ID | Due Date | Due Date Calendar Date | Transaction Currency | Balance in Transaction Currency |
1001111 | 10 | 1.65698E+12 | 07.07.2022 | EUR | 100 |
1001111 | 20 | 1.65698E+12 | 07.07.2022 | EUR | 250 |
1001111 | 30 | 1.65698E+12 | 07.07.2022 | EUR | 500 |
1001111 | 30 | EUR | -100 | ||
1001122 | 40 | 1.65698E+12 | 07.07.2022 | EUR | 100 |
1001122 | 50 | 1.65698E+12 | 07.07.2022 | EUR | 500 |
1001133 | 60 | 1.65698E+12 | 07.07.2022 | EUR | 1000 |
1001133 | 60 | EUR | -500 | ||
1001133 | 70 | 1.65698E+12 | 07.07.2022 | EUR | 300 |
1001133 | 70 | EUR | -50 | ||
1001133 | 80 | 1.65698E+12 | 07.07.2022 | EUR | 200 |
Note: Due Date is in epoch format, Due Date Calendar Date is formatted as a "normal" date.
The entirely open Invoices are not causing any issues. The one I am interested in are the ones with partial payments (you can identify them by Customer Invoice ID appearing multiple times and a missing due date). The expected results for the due invoices tables are (cases with partial payments are marked red):
Business Partner ID (Account ID) | Customer Invoice ID | Due Date | Due Date Calendar Date | Transaction Currency | Balance in Transaction Currency |
1001111 | 10 | 1.65698E+12 | 07.07.2022 | EUR | 100 |
1001111 | 20 | 1.65698E+12 | 07.07.2022 | EUR | 250 |
1001111 | 30 | 1.65698E+12 | 07.07.2022 | EUR | 400 |
1001122 | 40 | 1.65698E+12 | 07.07.2022 | EUR | 100 |
1001122 | 50 | 1.65698E+12 | 07.07.2022 | EUR | 500 |
1001133 | 60 | 1.65698E+12 | 07.07.2022 | EUR | 500 |
1001133 | 70 | 1.65698E+12 | 07.07.2022 | EUR | 250 |
I'd be very grateful if someone of this great community could help me solve this issue. Many thanks already in advance!
Solved! Go to Solution.
HI @JoZu
Here is a sample file with the solution https://we.tl/t-BJ0BtLRpwW
Due Transation Currency =
SUMX (
SUMMARIZE ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID],'Accounts Receivable'[Due Date Calendar Date] ),
CALCULATE (
VAR CurrentDueDate = SELECTEDVALUE ( 'Accounts Receivable'[Due Date Calendar Date] )
RETURN
IF (
CurrentDueDate <= TODAY ( ) && CurrentDueDate <> BLANK ( ),
CALCULATE (
SUM ( 'Accounts Receivable'[Balance in Transaction Currency] ),
ALLEXCEPT ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID] )
)
)
)
)
Thank you very much @tamerj1 ! The measure works perfectly fine and does what its supposed to! I forgot to mention, that the date restriction was done with a filter on the visual. You even included this into the measure - even better.
I am more than happy with this solution and the very fast reply - THANKS!
HI @JoZu
Here is a sample file with the solution https://we.tl/t-BJ0BtLRpwW
Due Transation Currency =
SUMX (
SUMMARIZE ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID],'Accounts Receivable'[Due Date Calendar Date] ),
CALCULATE (
VAR CurrentDueDate = SELECTEDVALUE ( 'Accounts Receivable'[Due Date Calendar Date] )
RETURN
IF (
CurrentDueDate <= TODAY ( ) && CurrentDueDate <> BLANK ( ),
CALCULATE (
SUM ( 'Accounts Receivable'[Balance in Transaction Currency] ),
ALLEXCEPT ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID] )
)
)
)
)
User | Count |
---|---|
87 | |
72 | |
69 | |
64 | |
56 |
User | Count |
---|---|
99 | |
91 | |
80 | |
74 | |
64 |