Calculate Average Days Between Distinct Dates

[Ryan_Lessig]

Hello,

I am trying to calculate the average number of days between transaction dates for a specific vendor, where there are multiple transactions on the same date.

I need to do this on the front end and not in the load script so that if the user wants to analyze a certain time period, they can filter to specific years, months, weeks, etc. and the calculation changes.

Example table below:

PO Vendor Name	PO #	PO Issue Date	Days Between (not in data)
Test Vendor	1234	1-1-2020	blank
Test Vendor	1235	1-1-2020	0
Test Vendor	1236	1-15-2020	14
Test Vendor	1237	1-15-2020	0
Test Vendor	1238	1-20-2020	5

**Days between is just a column I added to show what I am trying to do, it is not actually a field in the data.

Here is the calculation I tried but it does not work, because if the Days Between is 0, then it skews the average. I need the script to look at DISTINCT days between. So it should be the average of (14 and 5) / 2 = 9.5 days. Not (0+14+0+5) / 4 = 4.75.

avg(aggr([PO Issue Date] - above([PO Issue Date]),[PO Vendor Name],[PO #]))

I tried adding DISTINCT to the formula above but it still did not work.

Any help would be appreciated.

Thanks!

[Vegar · Accepted solution]

What if you calculate it by a different approach? Like this. 

=(max([PO issue date]) - min([PO issue date])) / (count(distinct [PO issue date]) - 1)

[Ryan_Lessig]

@Vegar  - sometimes I make something very simple, very complicated. That worked perfectly. Thank you!