Calculating a weighted average if date within range

[Telli]

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

[Telli]

Much appriciated @amitchandak. Will try it during the weekend 

[v-easonf-msft · Accepted solution]

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

[amitchandak]

@Telli , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...