Power BI DAX Payment Run logic

[Anonyme94]

Hi everyone, 
I'm reposting a request that has been addressed differently in the past : 

As per the client's account payable process, payment for all the due invoices are done as per the following payment cycle:

10th of every month

15th of every month

20th of every month

end of every month

For example :

If a invoice has due date of 2nd of month then it will be paid on 10th of that month and if a invoice has due date 12th of month then it will be paid on 15th of that month.

I'm struggling to determine the closest payment date based on due date. Are any of you faced this issue before ? 
Any suggestion would help to go further 🙂 

Thanks. 

[johnt75 · Accepted solution]

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]
)

[AmiraBedh · Accepted solution]

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.

[Anonyme94]

Hi @AmiraBedh 

Magnifique !  Many thanks for your feedback, that works as expected

[AmiraBedh]

Glad to have helped 🙂

[grazitti_sapna]

Hi @Anonyme94,

Firstly let's assume end of month as last day of your month.

Assuming your table name is invoices and it has a duedate column as well.

Let's first create start and end date of the due date month i.e. 1st and last date of the month, and then extracting the nexy payment date based on the crieteira you mentioned.

You can use below DAX for the same.

NextPaymentDate =
VAR Due = Invoices[DueDate]
VAR DayOfMonth = DAY(Due)
VAR MonthStart = DATE(YEAR(Due), MONTH(Due), 1)
VAR EndOfMonth = EOMONTH(Due, 0)
RETURN
SWITCH(
TRUE(),
DayOfMonth <= 10, DATE(YEAR(Due), MONTH(Due), 10),
DayOfMonth <= 15, DATE(YEAR(Due), MONTH(Due), 15),
DayOfMonth <= 20, DATE(YEAR(Due), MONTH(Due), 20),
DayOfMonth > 20, EndOfMonth
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Anonyme94]

Hi @grazitti_sapna 

Merci beaucoup ! 
It works, thanks for the tips on splitting day, month & year 

[johnt75 · Accepted solution]

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]
)

[Anonyme94]

Hi @johnt75 

Thanks a lot for your support and tips, it works !