DAX Payment Run logic

[Anonymous]

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.

Requirement :

Now, client has requirement that suppose a invoice if due on 15th of this month and I run a payment run today (out of regular payment cycle) i.e. 08-12-2019 and clear(pay) that invoice (ideally, it should be cleared in 15th payment cycle) then clearing date should be updated as of today and it should not be considered in 15th payment run cycle (Maybe, we flag this as already paid and corresponding payment date).

Please let me know how this can be thrugh DAX.

[mwegener]

Hi @Anonymous,

what are the requirements for Power BI?

The existing requirements relate to the payment system or not?

Please give Kudos for support.

[Anonymous]

I want to implement above requirement through DAX.

Payment System is as defined above by client.

[mwegener]

Hi @Anonymous ,

could you please provide some sample data, what your source data looks like and what your result should look like?

Please give Kudos for support.

[Anonymous]

Sample data is already there in the screenshot.

I assume this should be sufficient for defining the DAX statememt.

[TomMartens]

Hey @Anonymous ,

I guess this will become quite difficult because neither DAX nor M will persist data, meaning whenever data will be refreshed, the DAX (calculated column) or the M script will evaluate the script for the same row once again.

So, assuming there is due date 2019-12-12, and data will be refreshed on the 12th, this row should be considered for clearance on the 12th?

What will happen, if the data will be refreshed on the 15th, the same row will be considered for clearance on the 15th, as it seems your data model does not contain any information about "runs".

I guess as @mwegener was asking for sample data, was the question for providing reusable sample data, as a screenshot is not reusable in an easy way.

Please consider to provide a pbix and or xlsx file(s), that contain sample data and represent your data model. Upload the file(s) to onedrive or dropbox and share the link.

Regards,

Tom

[mwegener]

I thought it's clear that Power BI can only query data but can not persist data.

So the information about the payment run etc. must be present in the data source.

I asked for sample data to understand what the input data is and what the result should be.

[mwegener]

Okay, I do not understand your request.
Hopefully you find someone who does it.