Forecasting payment date with calculated average

[ManPi]

Hi all,

I could use some help with calculating a forecasted paymentdate of our customers. I have calculated the average paymentterm from the past payments. Average paymentterm = DATEDIFF (Boekingen[Boekdatum}.{Date};Boekingen{Betalingsdatum}{Date};Day). This formula gives me an average per customer.

In the new formula a want to calculate the outstanding invoices per Invoice belonging to a customer.

Customer	Invoice number	Invoice date	Payment date	Days untill paid	Average paymentterm	Expected payment date
1	1	01-01-2020	05-01-2020	5	6
1	2	01-01-2020	07-01-2020	7	6
1	3	05-01-2020			6	11-01-2020
2	4	01-01-2020	30-01-2020	30	18
2	5	02-01-2020	07-01-2020	6	18
2	6	10-01-2020			18	28-01-2020
3	7	15-01-2020	30-01-2020	15	15
3	8	01-02-2020			15	16-02-2020

Could you help me with the right formula?

Kind regards,

Mandy Pigmans

[v-alq-msft]

Hi, @ManPi 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[ManPi]

Hi Allen,

Sorry for the late reply, but due to the developments with the Coronavirus I didn't had the time to try your solution any sooner.

Thanks for your help. It was the right solution. 

Kind regards,

Mandy 

[v-alq-msft]

Hi, @ManPi 

Based on your description, I created data to reproduce your scenario.

You may create a measure as follows.

Expected payment date = 
var _value = 
CALCULATE(
    SUM('Table'[Average paymentterm]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Customer] = MAX('Table'[Customer])
    )
)-
CALCULATE(
    SUM('Table'[Days paid]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Customer] = MAX('Table'[Customer])
    )
)

return
IF(
    ISBLANK(MAX('Table'[Payment date])),
    MAX('Table'[Invoice date])+_value,
    BLANK()
)

Finally you may use a table visual to display the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ManPi]

Dear Allan,

Thank you for your replay. At this point the formula shows a blank collum. Could you send me a measure for the Average paymentterm? It isn't given in the raw data, but it should be a calculated number.

I think the problem starts there, because i don't see the average per customer on the individual lines per Invoice.

Kind regards,

Mandy

[v-alq-msft · Accepted solution]

Hi, @ManPi 

There are two ways.

First way: You may create a calculated column and a measure as follows.

Average paymentterm Column = 
CALCULATE(
        AVERAGE('Table'[Days paid]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Customer] = EARLIER('Table'[Customer])&&
            NOT ISBLANK('Table'[Payment date])
        )
)

Expected payment date = 
var _value = 
CALCULATE(
    SUM('Table'[Average paymentterm Column]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Customer] = MAX('Table'[Customer])
    )
)-
CALCULATE(
    SUM('Table'[Days paid]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Customer] = MAX('Table'[Customer])
    )
)

return
IF(
    ISBLANK(MAX('Table'[Payment date])),
    MAX('Table'[Invoice date])+_value,
    BLANK()
)

Second way: You may create two measures as below.

Average paymentterm Measure = 
CALCULATE(
        AVERAGE('Table'[Days paid]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Customer] = MAX('Table'[Customer])&&
            NOT ISBLANK('Table'[Payment date])
        )
)

Expected payment date Measure = 
var _value = 
CALCULATE(
    SUMX(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Customer] = MAX('Table'[Customer])
        ),
        [Average paymentterm Measure]
    )
)-
CALCULATE(
    SUM('Table'[Days paid]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Customer] = MAX('Table'[Customer])
    )
)

return
IF(
    ISBLANK(MAX('Table'[Payment date])),
    MAX('Table'[Invoice date])+_value,
    BLANK()
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ManPi]

Hi Allan,

Unfortunately the measure doesn't work when there are more than one unpaid invoices. The SUM of the average paymentterm colum gets a value for every invoice and with that the difference with the SUM of the Days paid is to high. 

Do you know a nother sollution?

Kind regards,

Mandy

[Greg_Deckler]

So what are your expected results from that data?

[ManPi]

Hi Greg,

In my example I want to calculate the last two colums.

Kind regards,

Mandy