Calculating Storage Occupancy from History of Multiple Item Stages - Measure or Calculated Column?

[Barhomopolis]

Hello Everyone,

I have this dataset (click here) of items with time stamped stages. Some stages mean the item is occupying space in the storage. The other stages mean it's not. I need to use Power Pivot 2016 (not Power BI) to produce the chart below for avg. storage occupancy over time for the same time period of the same week day (we call it "time of week" for short). For instance, the chart should answer the question, "How many items on average do we have in storage on Sundays between 12 & 4 PM?" I also need to show percentiles on the same chart as below.



Before going into averages and percentiles, I first tried to create this measure to calculate the occupancy itself at any given "time of week":

Occupancy:=CALCULATE(DISTINCTCOUNT('Source data'[ItemKey]),'Source data'[Stage] <> "Arrival" && 'Source data'[Stage] <> "Transport"),'Source data'[DateKey]))-CALCULATE(DISTINCTCOUNT('Source data'[ItemKey]),'Source data'[Stage] = "Arrival" || 'Source data'[Stage] = "Transport")

I was getting negative numbers, which is impossible. Then I realised I have to include items that entered the storage in the past and haven't left yet, but I couldn't figure out how to do it in Power Pivot 2016. It looks like there are new DAX functions in Power BI that do just this, but we can't use Power BI.

When I tried to calculate it in a calculated column:

=CALCULATE(DISTINCTCOUNT('Source data'[ItemKey]),'Source data'[Stage] <> "Arrival" && 'Source data'[Stage] <> "Transport",'Source data'[DateKey]<=EARLIER('Source data'[DateKey]))-CALCULATE(DISTINCTCOUNT('Source data'[ItemKey]),'Source data'[Stage] = "Arrival" || 'Source data'[Stage] = "Transport",'Source data'[DateKey]<=EARLIER('Source data'[DateKey]))

I got an out of memory error, although the dataset is fewer a hundred thousand rows.

I would appreciate the help.

 

[Barhomopolis]

To clarify more:
I'm trying to calculate how many items are occupying storage space at the time of calculation. I'm trying to do this by counting how many items came in (since the oldest time in the dataset all the way until the time of calculation), then subtracting how many came out (also from the oldest time until the time of calculation).

Each item goes through several time-stamped stages.

The stage "Arrival" means it's not yet occupying storage space.
The stage "Transport" means that after the "Transport" time stamp, an item is not occupying space anymore, but right until the "Transport" time stamp, it was occupying storage space.
All other stages happen after "Arrival" and before "Transport" and they mean that the item is occupying storage space.

The occupancy needs to be measured for four-hour intervals (called "time of week", e.g. Sunday 12AM - 4AM or Wednesday 8PM to Thursday 12AM). Then it needs to be averaged (and percentiles calculated) over all the occurrences of each time interval in the dataset to produce the chart above but with fewer bars (only one bar per 4-hour interval, no minor tick marks on the axis).

The aggregation (averages and percentiles) is easy. I just don't know how to count correctly XD

The dataset sample is here.

Thanks in advance!