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.CustomerInvoice numberInvoice datePayment dateDays untill paidAverage paymenttermExpected payment date1101-01-202005-01-202056 1201-01-202007-01-202076 1305-01-2020 611-01-20202401-01-202030-01-20203018 2502-01-202007-01-2020618 2610-01-2020 1828-01-20203715-01-202030-01-20201515 3801-02-2020 1516-02-2020 Could you help me with the right formula? Kind regards, Mandy Pigmans

Frequent Visitor

6 years ago

Solved

Forecasting payment date with calculated average

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

Need Help

v-alq-msft

6 years ago

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.

8 Replies

Greg_Deckler
Community Champion
6 years ago
So what are your expected results from that data?
- ManPi
  Frequent Visitor
  6 years ago
  Hi Greg,
  
  In my example I want to calculate the last two colums.
  
  Kind regards,
  
  Mandy

v-alq-msft

Community Support

6 years ago

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

Frequent Visitor

6 years ago

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

Community Support

6 years ago

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.

v-alq-msft
Community Support
6 years ago
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
  Frequent Visitor
  6 years ago
  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

Forum Discussion

Forecasting payment date with calculated average

8 Replies

Related content

Forecasting with DAX

Forecast Calculation

Calculating Forecasting Accuracy

Mortgage Payment Calculation

Calculate difference between versions of Forecast data