Predict payment date

[mrleijzer]

Hello,

I'm trying to get to predict when invoices are paid using average payment days per customer.

I found a topic just like it: https://community.powerbi.com/t5/Desktop/Forecasting-payment-date-with-calculated-average/m-p/958893...

I am using direct query so I can't calculate in a column.

I therefore tried the 2 measure alternative and edited the variables like this:

Average paymentterm Measure = 
CALCULATE(
        AVERAGE(Boekingen[Betalingsdagen]);
        FILTER(
            ALLSELECTED('Boekingen');
            Boekingen[KorteNaam] = MAX(Boekingen[KorteNaam])&&
            NOT ISBLANK(Boekingen[Betalingsdatum])
        )
)

Expected payment date Measure = 
var _value = 
CALCULATE(
    SUMX(
        FILTER(
            ALLSELECTED('Boekingen');
            'Boekingen'[KorteNaam] = MAX('Boekingen'[KorteNaam])
        );
        [Average paymentterm Measure]
    )
)-
CALCULATE(
    SUM(Boekingen[Betalingsdagen]);
    FILTER(
        ALLSELECTED('Boekingen');
        'Boekingen'[KorteNaam] = MAX('Boekingen'[KorteNaam])
    )
)

return
IF(
    ISBLANK(MAX('Boekingen'[Betalingsdatum]));
    MAX('Boekingen'[Boekdatum])+_value;
    BLANK()
)

Now, the measures look good and without error.

Except when I insert the Expected payment date Measure in a matrix visual it just keeps loading and loading and it won't show the data (expected payment date).

What am I doing wrong here?

Thanks in advance!

[v-juanli-msft · Accepted solution]

Hi @mrleijzer 

As for the thread

https://community.powerbi.com/t5/Desktop/Forecasting-payment-date-with-calculated-average/m-p/958893...

my solution is as below, please check if my measures can work better on your example.

Meausres:

days =
IF (
    NOT (
        ISBLANK (
            MAX ( [Payment date] )
        )
    ),
    DATEDIFF (
        MAX ( [Invoice date] ),
        MAX ( [Payment date] ),
        DAY
    ) + 1
)


average = AVERAGEX(FILTER(ALLSELECTED('Table'),[Customer]=MAX([Customer])),[days])

expected date = IF(ISBLANK(MAX([Payment date])),MAX([Invoice date])+[average])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mrleijzer]

Thanks! With a little tweaking I got it to work!

[v-juanli-msft]

Hi @mrleijzer 

Open View->Performance Analyzer to monitor the performance of your report.

To improve performance, you could try several ways as this article listed, like adding filters on the reports,,ect.

If you only add Boekingen[KorreNaam] in the table visual, then you can modify the measure to improve performance,

Average paymentterm Measure = 
CALCULATE(
        AVERAGE(Boekingen[Betalingsdagen]);
        FILTER(
            'Boekingen';
            NOT ISBLANK(Boekingen[Betalingsdatum])
        )
)

also modify the measure as below

Expected payment date Measure = 
var value1 = 
    SUMX(
        FILTER(
            ALLSELECTED('Boekingen');
            'Boekingen'[KorteNaam] = MAX('Boekingen'[KorteNaam])
        );
        [Average paymentterm Measure]
    )

var value2=
    SUM(Boekingen[Betalingsdagen])

var value3=value1-value2

return
IF(
    ISBLANK(MAX('Boekingen'[Betalingsdatum]));
    MAX('Boekingen'[Boekdatum])+value3;
  )

You could share a sample for further analysis.

Best Regards

Maggie