Forum Discussion

New Member

7 years ago

Solved

timesheet Measure

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

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

1 Reply

Microsoft Employee

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

Related content