Forum Discussion

dbmahn's avatar
dbmahn
Frequent Visitor
2 years ago
Solved

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.2023InvoiceA100500
01.03.2023InvoiceA1015001
01.04.2023InvoiceA10025002
01.02.2023InvoiceA10035003
01.02.2023InvoiceA10045004
15.03.2023InvoiceA10055005
17.03.2023InvoiceA10065006
19.05.2023InvoiceA10075007
20.10.2023InvoiceA10085008
01.08.2023InvoiceA10095009
19.07.2023InvoiceA100105009
23.08.2023InvoiceA100115008
24.08.2023InvoiceA100125007
01.09.2023InvoiceA100135006
01.10.2023InvoiceA100145005
02.03.2023PaymentA100-500
30.03.2023PaymentA101-5001
30.04.2023PaymentA1002-5002
02.03.2023PaymentA1003-5003
02.03.2023PaymentA1004-5004
13.04.2023PaymentA1005-5005
15.04.2023PaymentA1006-5006
17.06.2023PaymentA1007-5007
18.11.2023PaymentA1008-5008
30.08.2023PaymentA1009-5009
17.08.2023PaymentA10010-5009
21.09.2023PaymentA10011-5008
22.09.2023PaymentA10012-5007
30.09.2023PaymentA10013-5006
30.10.2023PaymentA10014-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!