cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndreLap Regular Visitor
Regular Visitor

Time between two stamps

Dear Data gods,

Currently I'm working on a report in which we can monitor our machines active times (might be a bit difficult to explain). And I want to put that in a table, to show the uptime / downtime per hour. My dataset is shaped as followed:

Datestamp = Date of the record
Machine ID = ID of the machine (unique)
Status = Online/Busy/Offline
starttime = timestamp of the start of a new status
endtime = timestamp of the end of a status (just before it changes to another status)
duration = second between start and endtime

Each time a machine changes status, it will upload a new row (of the previous status). So when a machine starts running at 08:00 AM but changes to busy at 09:24. It will upload a new record with:
1/1/2019, 141, Online, 08:00, 09:24, 5040

When putting this in a table/matrix it will show "1:24:00" at 08:00. 
How can i get it to show
08:00 - "1:00:00"
09:00 - "0:24:00" 


Your help is greatly appriciated! 

1 ACCEPTED SOLUTION

Accepted Solutions
AndreLap Regular Visitor
Regular Visitor

Re: Time between two stamps

Fix it with creating a new table and joining it with the current on. All in dax, see formula below Smiley Happy
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)
                )
    )
11 REPLIES 11
Super User
Super User

Re: Time between two stamps

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/3395...

 

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


AndreLap Regular Visitor
Regular Visitor

Re: Time between two stamps

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. 


Super User
Super User

Re: Time between two stamps

@AndreLap - OK, I came up with something:

 

Measure = 
VAR __currentHour = HOUR(MAX('Table2'[Hour]))
VAR __startHour = HOUR(MAX('Table1'[Start]))
VAR __endHour = HOUR(MAX('Table1'[End]))
VAR __table = GENERATESERIES(__startHour,__endHour,1)
VAR __table1 = ADDCOLUMNS(__table,"__minutes",
    SWITCH(TRUE(),
        __startHour < __endHour && [Value] <> __endHour,60,
        __startHour < __endHour && [Value] = __endHour,MINUTE(MAX('Table1'[End])),
        MINUTE(MAX(Table1[Start]))
    )
)
RETURN
MAXX(FILTER(__table1,[Value] = __currentHour),[__minutes])

See Table1 and Table2, Page 1 of attached.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


AndreLap Regular Visitor
Regular Visitor

Re: Time between two stamps

That is exactly what I'm looking for, but when using the code on my data (after changing the values to fit my dataset), it returns the following error:

mdxScript(model)(141,15) Calculation error in measure 'PowerBI_statusupdates'[Time Per Hour'] : The arguments in GenerateSeries function cannot be blank. 

I'm using a Date -> Time hierachy in the matrix. From both a Date table and a Time table. 
So when the user selects a machine(id), it will show all available dates within the range, with all hours beneath them. 

when adding the date to your solution, it gives me the same error

Super User
Super User

Re: Time between two stamps

Sounds like you have some blanks in your Start and End times, you'll have to check for that and, if so, probably set the end hour to the start hour I imagine.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


AndreLap Regular Visitor
Regular Visitor

Re: Time between two stamps

The problem seems to be with the Date Table. The date table is based on values from another table until today. The status table only contains data from the last year, but the date table more then 3 years. 

Any ideas on how to handle this within the formula? Because I would like to use the date table to filter the report (it contains data from other tables as well, and one date filter would be ideal)

Highlighted
AndreLap Regular Visitor
Regular Visitor

Re: Time between two stamps

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 Smiley Happy. Thanks greg for the solution! 

Super User
Super User

Re: Time between two stamps

Sweet! 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


AndreLap Regular Visitor
Regular Visitor

Re: Time between two stamps

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:MMSmiley Frustrated
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

PBIexample.png