Measure to convert Timestamps to Dynamic Buckets

[Ascalon]

Hi all,

I have a production process where unique products (ProductID) move through a series of six stages (StageID) during the course of a single day.

When a ProductID moves to a new stage, a timestamp is generated - e.g.

Timestamp	ProductID	StageID
06:45:22	1	1
06:46:12	2	1
06:52:33	1	2
06:52:33	3	1
07:04:24	2	2
07:06:14	1	3
07:06:14	3	2
07:12:12	2	3
...	...	...

I am trying to create a measure that counts all ProductIDs by current StageID when a given timestamp is selected.

My current measure is:

Count( Distinct {<Timestamp={"<=$(=Max(Timestamp))"}>} ProductID)

It gets me halfway there - it counts all unique ProductIDs up until the point in time selected. But it doesn't provide their current StageID buckets. This is where I get stuck.

The key problem is that I need the MAX(StageID) to evaluate severally within the set (i.e. for each ProductID, for only the records on/before the timestamp) before aggregating the count. Most techniques I have tried effectively apply the MAX to the whole set (thus only counting the products in the most-advanced stage bucket).

Any guidance would be much appreciated.

Apologies for not posting a sample file, the data is commercially sensitive.

[Ascalon · Accepted solution]

For those interested, the solution I ended up devising was to combine the measure noted above with the following calculated dimension:

Aggr(FirstSortedValue({<Timestamp={"<=$(=max(Timestamp))"}>}StageID,-StageID),ProductID)

This takes the unique ProductIDs generated and buckets them into the highest StageID appearing for that ProductID before the timestamp.