Forum Discussion
Calculating a weighted average if date within range
Hi,
My Employer has asked me to calculate e "weighted average days" from invoices.
I assume that I would need to build a formula on the following logic
Sumproduct(Amount * Days(outstanding)/Total Amount
Where the variables would be constrained to:
given date is >issue date and <PayDate unless Paydate is Blank
| Invoice NO | Amount | Issue Date | PayDate |
| 1 | 1000 | 01.01.22 | 15.01.22 |
| 2 | 900 | 03.01.22 | null |
| 3 | 700 | 10.01.22 | 21.01.22 |
| 4 | 1100 | 15.01.22 | null |
14.01.22 -> 1000x13/2600 + 900x11/2600 + 700*4/2600 = 9,884 Days
15.01.22 -> 900x12/1600+700*5/1600 = 8,9375
I have tried write a bunch of formulas in DAX, but I come up short when I need to filter datex "between" Issue- and PayDate while also taking into account if Paydate is null.
with regards
telli
Hi， Telli
If your problem has been solved, you could accept the helpful answer as solution to close this thread.
Other community members will easily find the solution when they get the same issue.
If you haven't, please feel free to ask.
Best Regards,
Community Support Team _ Eason
3 Replies
- amitchandak
Super User
Much appriciated amitchandak. Will try it during the weekend
- v-easonf-msft
Community Support
Hi， Telli
If your problem has been solved, you could accept the helpful answer as solution to close this thread.
Other community members will easily find the solution when they get the same issue.
If you haven't, please feel free to ask.
Best Regards,
Community Support Team _ Eason
