Forum Discussion

mrleijzer's avatar
mrleijzer
Helper I
6 years ago
Solved

Predict payment date

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#M459418


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!

Need Help

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi mrleijzer 

    As for the thread

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

    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's avatar
      mrleijzer
      Helper I
      6 years ago

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