Lower the grain of a fact table into 15 minute buckets

[frano72]

Hi, I have a fact table that records the states of trucks in a mining circuit.  Trucks change state from operating, waiting, lunch break etc.  The start and end time that the truck is in this state is one row in the table.

I need to reduce the grain of this table to a fixed 15 minutes for a report that represents the last 15 minute interval.  So if one row of the table represented the state of a truck as "operating" for 60 minutes then changing the grain to 15 minutes will see four rows.  Obviously gets a little more tricky when the state passes over the interval boundaries.

Any ideas/patterns on to reduce grain ?

(yeah i know...its not a finance example right !)

[Greg_Deckler]

@frano72 - You should be able to use a variation of Open Tickets to achieve this. Open Tickets is by hour or day I think but it can support any time granularity level. If you can post sample data I can whip up a version for you. Please @ me if you reply.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

For the record, the operations problems are way more interesting to me than the finance ones!!

[frano72]

Thanks @Greg_Deckler - I'll have a look this morning at the tickets example.

[...maybe i should blog about operations examples to rebalance the weight of the worlds pbi finance examples !]

Here is a link to a subset of data in excel

https://www.dropbox.com/s/hczr2wxxh28pvpg/TruckStatus.xlsx?dl=0

Here's an even further subset.

StartTimeUTC	EndTimeUTC	ShiftIndex	Duration	Equipment ID	StatusID	Status Name
31/07/2020 20:00	1/08/2020 0:53	202008011	17614	53	418	Ready
1/08/2020 0:53	1/08/2020 1:38	202008011	2680	53	420	Delay
1/08/2020 1:38	1/08/2020 6:38	202008011	18030	53	419	Standby
1/08/2020 6:38	1/08/2020 7:35	202008011	3385	53	418	Ready
1/08/2020 7:35	1/08/2020 8:00	202008011	1491	53	420	Delay

Basically there are two 12 hour shifts per day and this is represented with the ShiftIndex column.  The last digit represents night or day.  So the above table is for 1st Aug 2020, Day shift.   I have only shown one piece of equipment.

You'll notice that the sum of the Duration is 12 hrs (in seconds).

So the challenge is splitting these five rows which represent truck "statuses" over the 12 shift into 15 minute buckets and have the total for each status type.

So output would be something like this.  Each row is now 15 minutes (900 seconds).

There has to be some date logic somewhere when the status changes within the 15 minute bucket.

StartTimeUTC	ShiftIndex	Duration	Equipment ID	Status_Ready	Status_Delay	Status_Standby
31/07/2020 20:00	202008011	900	53	900	0	0
31/07/2020 20:15	202008011	900	53	900	0	0
etc

(the problem is a bit more than this, but have distilled it down to the simplest form, that once solved i can scale it to the more complex).

Thanks !

[Greg_Deckler · Accepted solution]

@frano72 - I would read that blog. Here is a solution just for you as promised. See attached PBIX file below sig. Created this table:

Table 2 = 
    VAR __Min = MIN('Table'[StartTimeUTC])
    VAR __Max = MAX('Table'[EndTimeUTC])
RETURN
    GENERATESERIES(__Min,__Max,1/24/60*15)

And then this column in that table:

Column = MAXX(FILTER('Table',[StartTimeUTC]>=[Value] && [EndTimeUTC]>=[Value]),[Status Name])

I love the operations stuff the best. OTIF, MTBF, etc. Wish I could have fit more in my book DAX Cookbook. You can see the ones I did include here: https://github.com/gdeckler/DAXCookbook Chapter 9.

[frano72]

@Greg_Deckler - awesome work mate - thanks !!!!!!!

[Greg_Deckler]

@frano72 - Let me know when you start your blog! 🙂

[lbendlin]

Remember that a DateTime field is actually in decimal format with the integers as the days and the fractions as the hours and minutes etc.

Bucketing is very easy by applying some simple math

Half hour buckets:

ts_half = int([Timestamp]*48)/48

Quarter hour buckets:

ts_quart = int([Timestamp]*96)/96

etc.

[amitchandak]

@frano72 , Not very clear.

Create a time table and have a bucket of 15 Min in that and use that

Time Table - https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/