That's a pretty advanced scenario, but is definitely possible. I would tackle this with a date island in the data model. I'm assuming you already have a regular calendar attached to your Fact table. Clone this calendar and rename the fields so it's not linked to anything in the data model.
LOAD * RESIDENT Calendar;
Your expression will need to incorporate both calendars (the regular one and this new island) and will be pretty heavy because it will need to use nested aggr statements.
if(min(total <Worknro> Timestamp) <= DateIsland.Timestamp,
if(Timestamp=max(total <Worknro,DateIsland.Timestamp> if(Timestamp<=DateIsland.Timestamp,Timestamp)) and max(WIP)=1,
The dimension of the table would be the date island date. An example is attached. If you find that this expression is too heavy, another approach could be to take the logic to the data model itself using intervalmatch and pre-aggregation. You would lose the ability to make dynamic filters (e.g. selecting a subset of work orders) but would definitely gain speed.
Thanks for the example.
Almost there. I now came up with problem that I have two or more status changes in one day.
WorkNo TimeStamp Status WIP ID W123 13.06.2014, 12:31:02.614 1 0 9453769 W123 16.06.2014, 13:39:17.661 1 0 9465562 W123 16.06.2014, 13:39:23.183 2 1 9465564 W123 16.06.2014, 13:39:46.334 K 0 9465571 W123 18.06.2014, 08:51:03.167 R 0 9480310
So in this case the work has never been in WIP stage in the end of any day. But now it shows for this WorkNo WIP status on 16.6 & 17.6. In the Load script TimeStamp is formatted to date format (DD.MM.YYYY).
In the Load script the data is loaded in "right" order. Or to say that in database the data is in timestamp order, every new status modification comes up with new timestamp (and unique ID).
So the question is how to handle this.. Should I create every second also to the DateIsland and Load the data with exact time? Or can the Load order used somehow? As the status of the work should be in the end of day the last status valid.