Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Also, can you suggest on how can I improve my report please.
Thank you for all the help and effort.
Regards,
Arvin
Solved! Go to Solution.
Hi @Anonymous,
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @Anonymous,
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @MFelix Thank you for your response, can you help me on how to do that? Sorry I am a newbie here.
Thank you.
Hi @Anonymous,
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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"
Thank you.
Hi @Anonymous,
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |