Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Potato_Ghost
Frequent Visitor

Finding Time Differences In One Column With Certain Conditions

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. 

 

Capture.PNG

7 REPLIES 7
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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. 

@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. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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! 

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!


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors