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

Help Time Issues

Hello,

 

I am facing a problem with regards on the dashboard/report I am creating, the report is basically a Attendance Monitoring report, I would like to get the total hours of each employees, their absences, their lates, etc.

 

I am having a problem on the TIMEDIFF function of MySQL, please see attached screenshot.

 

attendance_pic.png

 

Also, can you suggest on how can I improve my report please.

 

Thank you for all the help and effort.

 

Regards,

Arvin

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Help Time Issues

Hi @alopez,

 

The information is different from what you add in you first post, the first record was 9:00 PM = 21:00 (24 Hours) in this you have 9:00 AM = 9:00 (24 Hours) so in fact this is different hours please check how you get the format of the time in / time out, I believe that is why you are having issues in calculating the corrects times.

 

Mfelix



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

Proud to be a Datanaut!




6 REPLIES 6
Super User
Super User

Re: Help Time Issues

HI @alopez,

 

Why don't you do you MySQL iwthout the TimeDiff formula and do that in the power query editor, using the time formulas?

 

Regards,

 

MFelix



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

Proud to be a Datanaut!




alopez Regular Visitor
Regular Visitor

Re: Help Time Issues

Hello @MFelix Thank you for your response, can you help me on how to do that? Sorry I am a newbie here.

 

Thank you.

Super User
Super User

Re: Help Time Issues

Hi @alopez,

 

Trying to replicate you data to give you some more information I notice that the main question you have is the in time is higher the the out time this is because you don't have a date/time in your format but only a time format, so if you look at the first record he enter at 9PM and leaves at 6AM this gives you a negative time. and the calculations are correct since from 6MA until 9PM is 14 hours.

 

You need to make the calculations of the dates, I assumed in my query that the duration was lower tha 24hours so only added 1 day to the hour. I have made a small query that you can copy to a blank query (used advance query editor) and made all the calculations you need.

 

Please check it.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDSNzIwNFfSUTIytDIwACIg08DMysACxIzVASoyQlIEFDYxRigyhisyRlUEMwluaGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Time_In = _t, Time_Out = _t]),
    Format = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time_In", type time}, {"Time_Out", type time}}),
    Duration_Calc = Table.AddColumn(Format, "Custom", each [Time_Out]-[Time_In]),
    Adjusted_Start = Table.AddColumn(Duration_Calc, "Time_In_Adjusted", each [Date]&[Time_In]),
    Adjusted_End = Table.AddColumn(Adjusted_Start, "Time_Out_Adjusted", each if [Time_Out]<[Time_In] then Date.AddDays([Date],1)&[Time_Out] else [Date]&[Time_Out]),
    Format_Adjusted = Table.TransformColumnTypes(Adjusted_End,{{"Time_In_Adjusted", type datetime}, {"Time_Out_Adjusted", type datetime}}),
    Duration_Adjusted = Table.AddColumn(Format_Adjusted, "Duration", each Duration.From ([Time_Out_Adjusted]-[Time_In_Adjusted])),
    Format_Duration = Table.TransformColumnTypes(Duration_Adjusted,{{"Duration", type duration}})
in
    Format_Duration

 

Regards,

 

MFelix



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

Proud to be a Datanaut!




alopez Regular Visitor
Regular Visitor

Re: Help Time Issues

Hello @MFelix

 

Kindly see the attached image, its the same issues I encountered when I used MySQL Function timediff.

 

let
Source = MySQL.Database("localhost", "attendance", [Query="SELECT * #(lf)FROM attendance_monitoring#(lf)", ReturnSingleDatabase=true]),
Format = Table.TransformColumnTypes(Source,{{"attendance_date", type date}, {"time_in", type time}, {"time_out", type time}}),
Duration_Calc = Table.AddColumn(Format, "Custom", each [time_out]-[time_in]),
Adjusted_Start = Table.AddColumn(Duration_Calc, "time_in_Adjusted", each [attendance_date]&[time_in]),
Adjusted_End = Table.AddColumn(Adjusted_Start, "time_out_Adjusted", each if [time_out]<[time_in] then Date.AddDays([attendance_date],1)&[time_out] else [attendance_date]&[time_out]),
Format_Adjusted = Table.TransformColumnTypes(Adjusted_End,{{"time_in_Adjusted", type datetime}, {"time_out_Adjusted", type datetime}}),
Duration_Adjusted = Table.AddColumn(Format_Adjusted, "Duration", each Duration.From ([time_out_Adjusted]-[time_in_Adjusted])),

 


#"Changed Type" = Table.TransformColumnTypes(Source,{{"attendance_date", type date}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","total_hours",Splitter.SplitTextByRepeatedLengths(5),{"total_hours.1", "total_hours.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Position",":",".",Replacer.ReplaceText,{"total_hours.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"total_hours.1", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"total_hours.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"total_hours.1", "total_hours"}})
in
#"Renamed Columns"

 

attendance_pic.png

 

Thank you.

Super User
Super User

Re: Help Time Issues

Hi @alopez,

 

The information is different from what you add in you first post, the first record was 9:00 PM = 21:00 (24 Hours) in this you have 9:00 AM = 9:00 (24 Hours) so in fact this is different hours please check how you get the format of the time in / time out, I believe that is why you are having issues in calculating the corrects times.

 

Mfelix



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

Proud to be a Datanaut!




alopez Regular Visitor
Regular Visitor

Re: Help Time Issues

Hey @MFelix Thank you !!! Its now working fine! Thank you for all the help!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 6 members 1,125 guests
Please welcome our newest community members: