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