Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi for all , ihave been asked to make a dash for the employee how cames fro 7:00 Am to 7:30 and how comes from 7:31Am to 8:00
and who comes starting 8:00
the excel source genreated from the attendees software give ,name,date ,Entry ,Leave
i need to get an average of employee how come in the first range and second range .....
below is an exemple of one employyee on different days of the monthreally i appreciate any help
Solved! Go to Solution.
Hi @moezm,
First, please create a date table in Power Query by pasting below M code into advanced editor.
let Source = List.Dates(#date(2018, 9, 3), 4, #duration(1, 0, 0, 0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Time", each List.Times(#time(7, 0, 0), 3, #duration(0, 0, 30, 0))), #"Expanded Time" = Table.ExpandListColumn(#"Added Custom", "Time"), #"Added Custom1" = Table.AddColumn(#"Expanded Time", "Time2", each [Time]+#duration(0,0,30,0)) in #"Added Custom1"
Then, in report view, create such a measure.
count employee = CALCULATE ( COUNT ( Sheet2[Name] ), FILTER ( ALL ( Sheet2 ), Sheet2[Date] = SELECTEDVALUE ( Query1[Date] ) && FORMAT ( Sheet2[Entry], "hh:mm:ss" ) >= FORMAT ( SELECTEDVALUE ( Query1[Time] ), "hh:mm:ss" ) && FORMAT ( Sheet2[Entry], "hh:mm:ss" ) < FORMAT ( SELECTEDVALUE ( Query1[Time2] ), "hh:mm:ss" ) ) ) + 0
Best regards,
Yuliana Gu
Hi @moezm,
First, please create a date table in Power Query by pasting below M code into advanced editor.
let Source = List.Dates(#date(2018, 9, 3), 4, #duration(1, 0, 0, 0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Time", each List.Times(#time(7, 0, 0), 3, #duration(0, 0, 30, 0))), #"Expanded Time" = Table.ExpandListColumn(#"Added Custom", "Time"), #"Added Custom1" = Table.AddColumn(#"Expanded Time", "Time2", each [Time]+#duration(0,0,30,0)) in #"Added Custom1"
Then, in report view, create such a measure.
count employee = CALCULATE ( COUNT ( Sheet2[Name] ), FILTER ( ALL ( Sheet2 ), Sheet2[Date] = SELECTEDVALUE ( Query1[Date] ) && FORMAT ( Sheet2[Entry], "hh:mm:ss" ) >= FORMAT ( SELECTEDVALUE ( Query1[Time] ), "hh:mm:ss" ) && FORMAT ( Sheet2[Entry], "hh:mm:ss" ) < FORMAT ( SELECTEDVALUE ( Query1[Time2] ), "hh:mm:ss" ) ) ) + 0
Best regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |