Debtors paid on time

[Oomsen]

I have the following columns and would like to create a measure which counts the days between payment and invoice date. At the end i would like to know which debtor pays on time and which don't. 

Date	Invoice number	Journalcode
1-3-2018	123456789	25	(Payment)
15-4-2018	123456789	70	(Invoice)

[v-cherch-msft]

Hi @Oomsen

You may refer to below measure:

Measure =
DATEDIFF (
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( ALL ( Table1 ), Table1[Date] < MAX ( Table1[Date] ) )
    ),
    MAX ( Table1[Date] ),
    DAY
)

Regards,

Cherie

[Oomsen]

I think it is working, thanks for that. But it doesn't give the result i expected. There are also transactions in the column which doesn't need to be count. I'm only looking for sales invoices and payments which can be based on journalcode 25 (payment) and 70 (invoice). 

[v-cherch-msft]

Hi @Oomsen

Could you provide more details for your scenario so that i could help further on it?

Regards,

Cherie

[Oomsen]

For sure. My table is called transactions and hold all financial transactions of our current administration. The table includes data like Date, Amount, Client/Supplier, GL account, etc. My goal is to find out that my customers are paying avrage on the 30 days that we have agreed. My idea wass to measure this bij counting the days between payment and invoice based on journalcode. 

[v-cherch-msft]

Hi @Oomsen

I add some data in the sample. You may refer to it and check if it matches what you need.

Measure2 =
VAR a =
    IF (
        MAX ( Table1[Journalcode] ) = 70,
        CALCULATE (
            MAX ( Table1[Date] ),
            FILTER (
                ALLEXCEPT ( 'Table1', Table1[Invoice number] ),
                Table1[Journalcode] = 25
            )
        )
    )
RETURN
    DATEDIFF ( a, MAX ( Table1[Date] ), DAY )

Regards,

Cherie

[Oomsen]

I think this is not correct, the results are like -982.  i also need notice that the transaction on journalcode 25 will always be later in time then 70.

[v-cherch-msft]

Hi @Oomsen

Could you explain how to get the results like -982? I think i'm not fully understand it. It's better you can give some sample data and how to get the expected results.

Regards,

Cherie

[Oomsen]

That was the result of the presented formule up here. 

Below some expamle data of one invoice and payment. the table includes a lot of these invoices and other transactions. That's why i had the idea to select bij journalcode. Journalcode 70 is the invoice and journalcode 25 is the payment. I want to calculate the amount of days between invoice and payment.  

Transactions

[v-cherch-msft · Accepted solution]

Hi @Oomsen

Add a measure to get the amount as below. Here is the sample file.

Amount =
VAR a =
    SUMMARIZE ( Table1, Table1[Journalcode], Table1[Invoice number], "b", [Diff] )
RETURN
    SUMX ( a, [b] )

Regards,

Cherie