Power BI DAX Payment Run logic

You could create a new column on your date table to flag whether a date is a payment date or not. You could create a column in DAX like

Is Payment Date =
DAY ( 'Date'[Date] )
    IN { 10, 15, 20 }
        || EOMONTH ( 'Date'[Date], 0 ) = 'Date'[Date]

or you could do it in Power Query or SQL depending on where your date table comes from.

You could then add a column to your invoices table to store the closest payment date, e.g.

Payment Date =
MINX (
    FILTER (
        ALL('Date'),
        'Date'[Is Payment Date] = TRUE ()
            && 'Date'[Date] >= 'Invoice'[Due Date]
    ),
    'Date'[Date]
)

Hello !

Thank you for posting on Microsoft Fabric community.

You need to create a CC for each invoice due date in the same month:

• If day ≤ 10 : pay on the 10th

• Else if day ≤ 15 : pay on the 15th

• Else if day ≤ 20 : pay on the 20th

• Else : pay on EOM

PaymentRunDate = 
VAR d = DAY ( Invoices[DueDate] )
VAR y = YEAR ( Invoices[DueDate] )
VAR m = MONTH ( Invoices[DueDate] )
RETURN
IF (
    ISBLANK ( Invoices[DueDate] ),
    BLANK(),
    SWITCH (
        TRUE(),
        d <= 10, DATE ( y, m, 10 ),
        d <= 15, DATE ( y, m, 15 ),
        d <= 20, DATE ( y, m, 20 ),
        EOMONTH ( Invoices[DueDate], 0 )
    )
)

you can find the solution in the attached pbix.