Forum Discussion
Number of Open items per calendar week
I have a transaction table in which invoices and payments are saved, each with the date of the transaction. The document number is the same for an invoice and the associated payment.
I would now like to display the outstanding invoice amounts per calendar week and how many different invoices are still open.
I have displayed the open amount using this DAX formula. But how do I calculate the number of invoices concerned?
still open = sum of Amount per DocumentID != 0
In my opinion, the difficulty lies in the fact that you can only recognize that the voucher is still open if the total per voucher number is 0.
Transactions:
DateTypeDocumentIDAmount
| 01.02.2023 | Invoice | A100 | 500 |
| 01.03.2023 | Invoice | A101 | 5001 |
| 01.04.2023 | Invoice | A1002 | 5002 |
| 01.02.2023 | Invoice | A1003 | 5003 |
| 01.02.2023 | Invoice | A1004 | 5004 |
| 15.03.2023 | Invoice | A1005 | 5005 |
| 17.03.2023 | Invoice | A1006 | 5006 |
| 19.05.2023 | Invoice | A1007 | 5007 |
| 20.10.2023 | Invoice | A1008 | 5008 |
| 01.08.2023 | Invoice | A1009 | 5009 |
| 19.07.2023 | Invoice | A10010 | 5009 |
| 23.08.2023 | Invoice | A10011 | 5008 |
| 24.08.2023 | Invoice | A10012 | 5007 |
| 01.09.2023 | Invoice | A10013 | 5006 |
| 01.10.2023 | Invoice | A10014 | 5005 |
| 02.03.2023 | Payment | A100 | -500 |
| 30.03.2023 | Payment | A101 | -5001 |
| 30.04.2023 | Payment | A1002 | -5002 |
| 02.03.2023 | Payment | A1003 | -5003 |
| 02.03.2023 | Payment | A1004 | -5004 |
| 13.04.2023 | Payment | A1005 | -5005 |
| 15.04.2023 | Payment | A1006 | -5006 |
| 17.06.2023 | Payment | A1007 | -5007 |
| 18.11.2023 | Payment | A1008 | -5008 |
| 30.08.2023 | Payment | A1009 | -5009 |
| 17.08.2023 | Payment | A10010 | -5009 |
| 21.09.2023 | Payment | A10011 | -5008 |
| 22.09.2023 | Payment | A10012 | -5007 |
| 30.09.2023 | Payment | A10013 | -5006 |
| 30.10.2023 | Payment | A10014 | -5005 |
Calendar:
Calendar =
ADDCOLUMNS (
CALENDAR (
DATE ( 2023, 1, 1 ),
DATE ( 2023, 12, 31 )
),
"YYYYWW", FORMAT ( [Date], "YYYY" ) & Format ( WEEKNUM ( [Date], 21 ), "00" ),
"WW/YY",
CONVERT ( WEEKNUM ( [Date], 21 ), STRING ) & "/"
& FORMAT ( [Date], "YY" ),
"DateKey", FORMAT ( [Date], "YYYYMMDD" )
)
Measure for Amount until today:
Amount until today =
CALCULATE (
SUM ( Transactions[Amount] ),
'Calendar'[Date]<= MAX ( 'Calendar'[Date] ),
ALL ( 'Calendar' )
)
Thank you!
for some bizarre reason the table renders taller than the matrix.
1 Reply
- lbendlin
Super User