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) ) )
I simply added IF BLANK() then 0 to the generateseries function. And then later IF 0 then BLANK() in the return to hide them again. Work perfect now :). Thanks greg for the solution!
Sweet!
- Anonymous7 years agoNot applicable
Hi Greg_Deckler ,
So after some testing, your solution didnt fit.
Whenever i have three state changes, of which 2 are of the same status, it takes the beginning of the first and the ending of the second change. In the sccreenshot below you can see my data:
Time Total is the duration in seconds, but formated to HH:MM:SS
Time per hour is your calculation for the minutes the machine was busy
Time Busy are all the seconds the status was busy (original from the dataset it self)
EDIT: so Time per hour and Time busy should be equal numbers, but never over 60- Greg_Deckler7 years agoCommunity Champion
OK, can you provide some sample data which exhibits the problem and what the output *should* be? Pretty sure the formula can be adapted. I also published it as a Quick Measure with a few updates. Not sure if the updates would solve the issue:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085