Forum Discussion
Time between two stamps
- Anonymous7 years ago
Fix it with creating a new table and joining it with the current on. All in dax, see formula below :)
Might not be the fastest with creating a fer variables, I still need to learn how to do it, but it gets the job done perfectly!PresenceGrouping = VAR __datetable = GENERATESERIES( MROUND(MIN(PowerBI_Status[Starttime + Date])-1/24, "1:00"), NOW()+1/24, TIME(1,0,0)) VAR __datetable1 = ADDCOLUMNS(__datetable, "_endtime", [Value] + 1/24) VAR __table2 = FILTER( CROSSJOIN(__datetable1, PowerBI_Status), [_endtime] >= PowerBI_Status[Starttime + Date] && [Value] <= PowerBI_Status[Endtime + Date]) VAR __table3 = SELECTCOLUMNS(__table2, [columns here, to much to show]) RETURN ADDCOLUMNS(__table3, "Seconds", SWITCH( TRUE(), [PresenceStart] < [Start] && [PresenceEnd] > [end], 3600, [PresenceStart] < [Start] && [PresenceEnd] <= [end], DATEDIFF([Start], [PresenceEnd], SECOND), [PresenceStart] >= [Start] && [PresenceEnd] > [end], DATEDIFF([PresenceStart], [end], SECOND), DATEDIFF([PresenceStart], [PresenceEnd], SECOND) ) )
Hmm, I have a couple thoughts on this I had to do some similar things for a manufacturing company with similar data. This was the origin of my MTBF artcle. http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Also, it seems similar to time interval type of issues which I have some quick measures for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
I also remember having to do some really funky "shift" calculations to calculate when a machine was running on what shift for how long. I'll have to see if I can dig those up. our case, it seems fairly tricky because I would assume that if a machine started at 07:59 AM and ran until 04:25 PM that you would want 1 minute for 7 and 60 minutes for all of the intervening hours and then 25 minutes for 4 PM, correct?
Hi Greg_Deckler the Datanaut
Thank you! I will need some time to go through all the posts, but i will report back with the results.
Also, yes at you question. That would be the ideal situation to achieve. That way we can analyse peak times, instead of manually calculating the duration per machine over the hours etc.