Finding Time Differences In One Column With Certain Conditions

[Potato_Ghost]

Hello Everyone! 

     I am hoping someone can please help me.  I am trying to find the duration of hours worked.  All of my date and time entries are in one column and I have a In and Out Column that tells me whether that time entry was someone comig in or out.  userid represents the people coming in and out. Does anyone know how I can use what I have to calculate total hours spent in the building? Any help regarding the matter would be very appreciated.  Please see below. 

[Greg_Deckler]

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Potato_Ghost]

Hi Greg! 

 Thanks for the response! I read through your article and found it very interesting.  Unfortunately I do not think that would help me in my case since your sample data has two date columns that you can find the difference between. I just have one.  Ideally if I could leverage the Query Editor to create a new Date and Time Column that had the previous time log in it.  I think I could go forward with your example. 

[Greg_Deckler]

@Potato_Ghost - In the article, I actually created the Previous Date/Time column with a calculated column in DAX using EARLIER. Then I could essentially just subtract them. 

[Greg_Deckler]

There is a technique in M to refer to a previous row, I mention it in my article here:

https://www.linkedin.com/pulse/fun-graphing-power-bi-part-sqrtpowersqrt82-deckler-microsoft-mvp-

but essentially the M code looks like:

=if [Index] = 0 then fnSierpinskiInit("0,1") else fnSierpinskiInit(#"Renamed Columns"{[Index]-1}[Sierpinski])

[Potato_Ghost]

Thanks Greg! I see what you mean with the DAX formula now.  I will sub my data in your DAX formula and keep you posted.  I will also giver your M code a go if that does not work for me.  Thanks again for your time and the help.  I will keep you posted! 

[Greg_Deckler]

Sure, the trick with the M code would be that you would want to sort it by your date and time then you will likely have to add a Table.Buffer so that you maintain the sorting and then you would add your Index column and then use that Index column in the way that I showed in the M code excerpt I supplied. Let me know!

[Potato_Ghost]

Hi Greg! 

     I gave it the old college try, but did not have success using your suggested methods.  There is no doubt in my mind that they would work, but they were way over my head. lol Also your Sierpinski Triangle cluster chart  blew my hair back. lol My boss is planning a DAX language course for me, and I also need to learn more on creating parameters and functions to get to the next level on the M-Code.  I wanted you to know that I did however finally come up with a solution to my problem. It was a bit of a work around, but it gives me total hours.  I took the following actions below to find total hours. 

1. Created a new conditional column named Time Reference.  I made sure the condition was never true, and my else was 12:00 am.

I then changed the data type to time. 

2. I created another column with the DateDiff function to give me minuites between time etries. Your idea in the DAX solution you provided for converting to minuites first instead of jumping straight into hours was a really useful in getting a more acurate number for hours. 

Conversion = DATEDIFF(auditdata[Time],auditdata[Time Reference ],MINUTE)

3. Then Created yet another column to covert to hours. 

Conversion (Hours) = (auditdata[Conversion]/60)*-1

4. This will probably make you roll your eyes lol but this was my formula to get total hours. This considers the IN and Out conditions, and works as long as someone clocks in and out an even number of times in a day. 

Total Hours = IF(auditdata[IN/OUT] = "IN", auditdata[Conversion (Hours)],auditdata[Conversion (Hours)]*-1)*-1

I am planning on making a condition in a measure to where if the count of clock ins and clock outs per day is an odd number, then make total hours 0, and return "Illigitimate." 

But thanks again for your suggestions Greg! I will be revisiting your solutions once I have more experience under my belt! 

         Best Wishes,  

                 Potato_Ghost