Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @mrleijzer
As for the thread
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.
Hi @mrleijzer
As for the thread
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.
Thanks! With a little tweaking I got it to work!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |