Forecasting payment date with calculated average

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.