Measure spanning multiple days/months

[keithboruta]

Hello all,

I have an app I am working on that it tracking shipments. We need to calculate the average inventory in transit per month. So if a shipment had a departure date of 1/1 and arrival of 1/10 and the value of the inventory was $1000, the average value for the month would be $333 ($1000 per day, 10 days out of 30 days in the month). This can get tricky as some shipments will be in transit for multiple months. What I tried to do was create a table of dates for every day in between earliest departure date and the latest arrival date (field called InventoryDate). Then I wrote this measure:

Sum(If(Date(InventoryDate) >= Date(Departure_Date) and Date(InventoryDate) < Date(Arrival_Date),[Shipment value]))/30

It kind of works, but it is not great and I am struggling to make similiar measures (like average shipment volume). 

Does anyone have a thought on how to handle this better?

Thanks!

[rbartley]

Hi,

Since the basis for the calculation does not appear to vary based on the selections made in the app, it would be more efficient to handle this in the load script. You can then directly reference the field in your visualizations.

[keithboruta]

Thanks Rbartley!

How would I do that? I am a bit unsure how to do that in a load script as I would need to calculate the daily value from one table as store it off in another table.

Or something else?