DAX Formula to Analyze Invoice Payment Status at Specific Dates

[digicontrolling]

Problem Statement: I am working with invoice data and want to categorize invoices based on their payment status as of specific dates. I would like to select a specific date from a Date dimension table and see how the situation looked at that particular moment. Additionally, I want to display how the payment status categories evolved over time.

Requirements:

1. Categorize invoices based on the age of the unpaid amount as of a specific date.
2. Select a specific date from a Date dimension table to analyze the payment status as of that date.
3. Display the evolution of the payment status categories over time.

Sample Data Structure: Here's a simplified example of the data:

InvoiceNumber	InvoiceDate	PaymentDate	InvoiceAmount	PaymentAmount
1	01.01.23	15.03.23	100	50
2	15.02.23	10.04.23	200	200
3	15.03.23	(null)	150	0

Objective: I want to create a measure that takes into consideration the selected date from the Date dimension table and categorizes the invoices accordingly. For example, if the selected date is 31.03.23, the measure should consider only the payments made up to that date and ignore the future payments.

Categories:

• "0-30 Days Due": If the unpaid amount is due for 0-30 days as of the selected date.
• "31-60 Days Due": If the unpaid amount is due for 31-60 days as of the selected date.
• "Over 60 Days Due": If the unpaid amount is due for over 60 days as of the selected date.
• "Paid": If the invoice is paid as of the selected date.

I appreciate any insights or guidance on how to achieve this using DAX in Power BI. Feel free to provide a simplified solution if necessary.

[Mahesh0016 · Accepted solution]

@digicontrolling I hope this helps you. Thank You.

Max Date = MAX(Slicer Date) / Min Date = MIN(Slicer Date)
E.g 
0-30 = calculate(
                [Total Sales],
                Filter(
                         ALL( Payments[Date] ),
                         Datediff ( Payments[Date]<[Max Date]/[Min Date] ) >=0
                         &&

                         Datediff ( Payments[Date]<[Max Date]/[Min Date] ) <31
                        )
)