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.
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 (Boekingen[Boekdatum}.{Date};Boekingen{Betalingsdatum}{Date};Day). This formula gives me an average per customer.
In the new formula a want to calculate the outstanding invoices per Invoice belonging to a customer.
Customer | Invoice number | Invoice date | Payment date | Days untill paid | Average paymentterm | Expected payment date |
1 | 1 | 01-01-2020 | 05-01-2020 | 5 | 6 | |
1 | 2 | 01-01-2020 | 07-01-2020 | 7 | 6 | |
1 | 3 | 05-01-2020 | 6 | 11-01-2020 | ||
2 | 4 | 01-01-2020 | 30-01-2020 | 30 | 18 | |
2 | 5 | 02-01-2020 | 07-01-2020 | 6 | 18 | |
2 | 6 | 10-01-2020 | 18 | 28-01-2020 | ||
3 | 7 | 15-01-2020 | 30-01-2020 | 15 | 15 | |
3 | 8 | 01-02-2020 | 15 | 16-02-2020 |
Could you help me with the right formula?
Kind regards,
Mandy Pigmans
Solved! Go to Solution.
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.
Hi, @ManPi
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Hi Allen,
Sorry for the late reply, but due to the developments with the Coronavirus I didn't had the time to try your solution any sooner.
Thanks for your help. It was the right solution.
Kind regards,
Mandy
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.
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
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.
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
So what are your expected results from that data?
Hi Greg,
In my example I want to calculate the last two colums.
Kind regards,
Mandy
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |