Events in Progress Date & Time

[m1nkeh]

Almost all the content online, as well as built in functions of PowerPivot/Dax/PowerBI etc. cater for date calculations that stop at day level.. I.e. YTD, QTD, MTD

I have a requirement to model event activity down to the minute, and it is absolutely driving me around the bend - most notably where the activity "crosses" midnight..

I need to answer the following:

• Number of starting events @ time x - DONE
• Number of ending events @ time x - DONE
• Number of events in progress @ time x - ONLY AT DATE

Ultimately the time will probably be reported hourly, or perhaps in 15-min segments - but the grain of the data is by second.

These events are very small, and fast... some might only last for a few seconds, and others a maximum of 3 or 4 minutes.

I have attached a workbook, with a simplified version of my model.. it contains a separate Date, and time dimension as well as a 'Fact' with a series of events - I have this model doing what it needs to do with regards to the Date upon which something happened... however, going below that level i am a bit stumped.

My thinking right now is that somehow i need to crossjoin the date and time dimension when analysing below the date level.. i need to give time some context as something i was getting yesterday was where 1am (for example) would be replicated upon all dates going into the future which obviously isn't right..

Initially, I'm just looking to get this working, however i know Marco / Alberto wrote a great paper on optimising Events in progress and my dataset is pretty large (many tens of thousands of events per hour) this is a concern!

Attached workbook: https://onedrive.live.com/redir?resid=6239C024A73A77F7!20866&authkey=!ACj9oxB3IxATkoo

Thanks!

 

[Matt Allington]

Yes, I was thinking it could be a problem. Filtering over data tables can be a problem. Using multiple columns can make it worse. You should try to find a calculated column solution instead. I haven't really looked back at the detail (don't really remember) but off the cuff, maybe you could create some sort of "duration" calc column which you could use somehow.

 

[m1nkeh]

Righto.. i pretty much got this working with the date / time run-time calc, but then ran in to a slightly new problem, yay! :/

For the same data, I need to do a SUM() operation for each record that has effectively been cross joined across "Time" at the grain level (minute), so that it can be rolled up to whatever the users want... 5, 15 minutes etc.

I got it working (sort of) but there was an issue whereby in order to calculate the correct aggregated value.. say, 5 minutes it would mean i need to work out exactly which minutes each event was "involved with", .. so i need to know exactly which events were operating at which minutes.. this got me thinking that the only solution would be to do a SUMX() over the data table, at the minute grain!! argh!!!!

I tried a couple of things, but ultimately what i need to do really is "for each minute in selection, sum the duration", almost to iterate over the minutes, instead of the data table - however i have absolutely zero clue how to do this as the two tables are not directly attached

I don't know, i'm at a bit of a loss now as i have 1 model where i have a (potential) VERY expensive run-time query to execute - still not written it, and i have another model where i have an absolute ton of processing to do ( like over a billion records for half a month)