topic Re: Calculating a weighted average if date within range in DAX Commands and Tips

Calculating a weighted average if date within range

Telli — Fri, 30 Sep 2022 18:10:54 GMT

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

Re: Calculating a weighted average if date within range

amitchandak — Sat, 01 Oct 2022 02:55:18 GMT

Telli , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Re: Calculating a weighted average if date within range

Telli — Sat, 01 Oct 2022 07:17:39 GMT

Much appriciated amitchandak. Will try it during the weekend

Re: Calculating a weighted average if date within range

v-easonf-msft — Mon, 10 Oct 2022 08:47:58 GMT

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