Analysis (duration, count, etc.) of a lot of time stamps for a machine

[Anonymous]

Hello all.  I am a newbie to Power BI but I am learning more everyday thanks to this forum. I apologize if I ask obvious/easy questions.

Ojective:  I would like to utilize the data described below to calculate how many On-Off cycles a machine has in a given period as well as an analysis of the duration (i.e., average "On" time, max/min "On" time, etc.).  Basically, I want to be able to trend these parameters over time and figure out when/if they change.  Also, there are three identical machines and I would like to compare each of their trends.  

Raw Data:  I have a spreadsheet of several hundred thousand rows which shows data for when a machine is On and when it is Off.  Everytime the machine turns On or Off, there is a corresponding timestamp; however, sometimes there are also "intermediate timestamps" when the machine status is unchanged from the previous timestamp but is logged nonetheless.  The raw data table looks something like this:

Timestamp		Status
1/23/2012 1:00:12 PM		On
1/23/2012 1:14:11 PM		Off
1/24/2012 12:44:11 PM		Off
1/25/2012 12:10:12 PM		Off
1/26/2012 11:40:12 AM		Off
1/26/2012 11:20:11 PM		On
1/26/2012 11:44:11 PM		Off
1/27/2012 12:02:12 AM		On
1/27/2012 12:26:12 AM		Off

Desired Final Product: I would like to be able to create a visualization/dashboard that shows for a particular month/quarter/year the number of On-Off cycles as well as the Average/Min/Max duration that the machine was on.  Again, there are actually three machines that I have this data for and another thing I would like to see/calculate is when (and for how long) they are both "On" at the same time (as well as a comparison of general overall trends).    

My guess on where to start: If the data were stricly composed of only points where the machine turned On or Off, then it seems like it would be fairly straight forward to just tell Power BI to create a new colum that would take the timestamp of the current line and subract the previous line.  This would give you how long that the machine was in that respective status (On or Off) and then I could just filter by status and Month/Quarter/Year.  Since there are "intermediate timestamps" where there is a data point but the status of the machine is unchanged from the previous timestamp, I'm guessing that I will need to have Power BI take the timestamp of the current line of data and filter the remaing data to show only timestamps that are later AND have a different status.  

Any assistance is apprteciated.  Thanks.

[mahoneypat · Accepted solution]

Not sure if you see multiple "On"s in a row (your data only shows "Off"s with that), but I believe this should handle that scenario too.  Here is a column expression that returns the time in minutes until the next Off, and ignores any duplicate "On"s in between.  You should be able to average/min/max this column to get your desired results.

On Duration =
VAR thisDT = 'Status'[Timestamp]
VAR nextoff =
    CALCULATE (
        MIN ( 'Status'[Timestamp] ),
        ALLEXCEPT ( 'Status', 'Status'[MachineNumber] ),
        'Status'[Timestamp] > thisDT,
        'Status'[Status] = "Off"
    )
VAR prevon =
    CALCULATE (
        MAX ( 'Status'[Timestamp] ),
        ALLEXCEPT ( 'Status', 'Status'[MachineNumber] ),
        'Status'[Timestamp] < thisDT,
        'Status'[Status] = "On"
    )
VAR checkduplicateon =
    ISBLANK (
        CALCULATE (
            MIN ( 'Status'[Timestamp] ),
            ALLEXCEPT ( 'Status', 'Status'[MachineNumber] ),
            'Status'[Timestamp] < thisDT
                && 'Status'[Timestamp] > prevon,
            'Status'[Status] = "Off"
        )
    )
VAR isfirstrow =
    thisDT
        = CALCULATE (
            MIN ( 'Status'[Timestamp] ),
            ALLEXCEPT ( 'Status', 'Status'[MachineNumber] )
        )
RETURN
    IF (
        ( isfirstrow || NOT ( checkduplicateon ) )
            && 'Status'[Status] = "On",
        DATEDIFF ( thisDT, nextoff, MINUTE )
    )

Pat

[JulianaMacedo]

Hi @mahoneypat 

I had a very similar problem and your solution really helped me, the only thing now is that I'm having a problem in performance... it runs out of memory if I want to calculate on my dataset if there are too many rows, for smaller subsets it's working fine.

So I was wondering If I changed the columns with datetime into two columns with date and time if that could help, but I did not succeed to change the code to work with those two columns instead, can you help me with it? If you were to do the same thing but if the Timestamp column was divided into date and time column instead, how would your code look like?

Obs: I have around 240,000 rows now (which theoritically power bi can handle, but has I said it's been impossible to handle with this dax formula)

[mahoneypat]

Please provide more info and some sample data with desired output in a copy/paste-able format. This article/videos may help too on best practices to handle datetime.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[JulianaMacedo]

I first tried to execute this as a calculated columns and once the amount of data increase it was impossible to process, so I adapted it into a measure and it partially works. Again, once the amount of data increases it doesn't work. I get timed out.. I also tried to evaluate the DAX on DAX studio to see if I could improve it, but I also get an error there saying "The end of the input was reached".

I have the same situation explained in the origin of the question, I need to calculate how much time a device is ON until the firts time it's OFF, not the last ON.

DateTime	Device ID	ON /OFF Status
2022-04-12 16:16	1	OFF
2022-04-12 16:19	1	OFF
2022-04-12 16:19	1	ON
2022-04-12 16:19	1	ON
2022-04-12 16:20	1	ON
2022-04-12 16:20	1	ON
2022-04-12 16:20	1	ON
2022-04-12 16:20	1	ON
2022-04-12 16:20	1	OFF
2022-04-12 16:20	1	ON
2022-04-12 16:24	1	OFF
2022-04-13 07:22	1	OFF
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:22	1	ON
2022-04-13 07:25	1	OFF

The DAX I have now looks like this:

TimeInState = 
var thisDT = SELECTEDVALUE(production[DateTime CET])
var nextOff = 
CALCULATE(
    MINX(production, production[DateTime CET]), 
    FILTER(
        ALLEXCEPT(production, production[Device ID]),
         production[DateTime CET] > thisDT),
         production[ON / OFF Status] = "OFF") 
         
var prevOn = 
CALCULATE(
    MAXX(production,production[DateTime CET]), 
    FILTER(
        ALLEXCEPT(production, production[Device ID]),
         production[DateTime CET] < thisDT),
          production[ON / OFF Status] = "ON") 

VAR checkduplication =
    ISBLANK (
        CALCULATE (
            MINX (production, 'production'[DateTime CET] ),
            FILTER(
                ALLEXCEPT ( 'production', 'production'[Device ID] ),
                'production'[DateTime CET] < thisDT
                && 'production'[DateTime CET] > prevon),
            'production'[ON / OFF Status] = "OFF"
        )
    )
VAR isfirstrow =
    thisDT
        = CALCULATE (
            MINX (production, 'production'[DateTime CET] ),
            ALLEXCEPT ( 'production', 'production'[PLC ID] )
        )

RETURN
    SWITCH(
        TRUE(),
        AND( isfirstrow || NOT ( checkduplication ),
        SELECTEDVALUE('production'[ON / OFF Status]) = "ON"),
        DATEDIFF ( thisDT, nextoff, SECOND )
    )

And then I format it like this:

Total Operating Time = 
VAR TotalHours = DIVIDE(SUMX(production,[TimeInState]) , 3600)
VAR HoursInt = TRUNC(TotalHours)
VAR MinutesTotal = (TotalHours - HoursInt) *60
VAR MinutesInt = TRUNC(MinutesTotal)
VAR SecondsTotal = ROUND((MinutesTotal - MinutesInt)*60,0)
RETURN
    FORMAT(HoursInt, "00")&"h, "&FORMAT(MinutesInt, "00")&"m, "&FORMAT(SecondsTotal, "00")&"s "

[Anonymous]

Thank you @mahoneypat...this seems to work for what I was asking for.  Quick followup question: if I want the duration delivered in HH:MM format instead of minutes, would I have to write another variable that divided by 60 and then takes the decimal remainder and converst it back into minutes or would there be a simpler way?

[mahoneypat]

You could do that as you describe with the MOD function.  Another approach is shown below.  This sums your new column, divides by 3600 to convert it to days in decimal form, converts that to DATETIME, and then formats that as "hh:mm".  If that goes above 24 hrs, you can use "d:hh:mm" to also show the # of days.

TotalDuration2 = FORMAT(CONVERT(DIVIDE(SUM('Status'[On Duration]), 24*60), DATETIME), "hh:mm")

[Anonymous]

@mahoneypat Thanks, that works.  Last question (I think): My spreadsheet is 250k rows and when I try to create this column, Power BI, it times out on me and tells me that it doesn't have enough memory.  I then tried doing 12k rows at a time with the intent of exporting the results to Excel and then repeat until all tags are done.  However, when I export, it looks like my timestamp changes slightly from the one that was imported (40888.3388484259 vs 40888.3388484144).  Any clue as to why this is happening or how I can stop it?  I had 20k lines that I exported to excel but vlookup only found about 9800 of them. 

[mahoneypat]

Please try this version.  It should be significantly faster.

OnDuration2 =
VAR thisDT = 'Status'[Timestamp]
VAR thisMachine = 'Status'[MachineNumber]
VAR thisMachinetable =
    FILTER ( 'Status', 'Status'[MachineNumber] = thisMachine )
VAR nextoff =
    MINX (
        FILTER (
            FILTER ( thisMachinetable, 'Status'[Timestamp] > thisDT ),
            'Status'[Status] = "Off"
        ),
        'Status'[Timestamp]
    )
VAR prevon =
    MAXX (
        FILTER (
            FILTER ( thisMachinetable, 'Status'[Timestamp] < thisDT ),
            'Status'[Status] = "On"
        ),
        'Status'[Timestamp]
    )
VAR checkduplicateon =
    ISBLANK (
        MINX (
            FILTER (
                FILTER (
                    thisMachinetable,
                    'Status'[Timestamp] < thisDT
                        && 'Status'[Timestamp] > prevon
                ),
                'Status'[Status] = "Off"
            ),
            'Status'[Timestamp]
        )
    )
VAR isfirstrow =
    thisDT = MINX ( thisMachinetable, 'Status'[Timestamp] )
RETURN
    IF (
        ( isfirstrow || NOT ( checkduplicateon ) )
            && 'Status'[Status] = "On",
        DATEDIFF ( thisDT, nextoff, MINUTE )
    )

Pat

[mahoneypat]

Thought about this some more, and there may be an even simpler approach.  Please try the last one and this one and report back.  This one checks if the row is an "On" row and then measures the time until the next status for that machinenumber (either on or off).  The sum of the new column should still be the same # of total minutes.

OnDuration3 =
VAR thistime = 'Status'[Timestamp]
VAR thisMachine = 'Status'[MachineNumber]
VAR nexttime =
    MINX (
        FILTER (
            'Status',
            'Status'[Timestamp] > thistime
                && 'Status'[MachineNumber] = thisMachine
        ),
        'Status'[Timestamp]
    )
RETURN
    IF ( 'Status'[Status] = "On", DATEDIFF ( thistime, nexttime, MINUTE ) )

Pat

[JulianaMacedo]

Hi!!
I'm curious... did it work? Because I'm trying the same solutions and I'm having the same problem with power bi, it says it rans out of memory... none of them are working.

[mahoneypat]

@Anonymous  Did either of these modified expressions improve things?

Pat