Forum Discussion

ManPi's avatar
ManPi
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 (Boeking...
Need Help
  • v-alq-msft's avatar
    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.

v-alq-msft's avatar
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's avatar
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's avatar
    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.

    • ManPi's avatar
      ManPi
      Frequent Visitor
      6 years ago

      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