Distinct count of employees in a DateTime matrix

[trinitykala]

Hello,
I’m currently working on "the time and attendance" tabular model (the bellow info is based on sample data). I have to say that working with Date and Time it makes me cry sometimes.

I have been recently asked to produce a new report which will show how many people, work in a store during the day.

Counting how many employees we have on the shop floor on every full hour. 

I would like to see something like that:

My sample data in my Fact table looks like that:

BranchNo	EmpKey	EventDate	PHP_StartDate	PHP_StartTime	PHP_EndDate	PHP_EndTime	PunchStartReason	PunchWorkedHrs
1234	Ala	12/07/2018	12/07/2018	21:00:00	13/07/2018	02:30:00	shift	6.17
1234	Ala	12/07/2018	13/07/2018	04:00:00	13/07/2018	06:30:00	shift	3.12
1234	Ala	13/07/2018	13/07/2018	21:00:00	14/07/2018	02:30:00	shift	6.2
1234	Ala	13/07/2018	14/07/2018	04:00:00	14/07/2018	06:30:00	shift	3.2
1234	Mat	12/07/2018	12/07/2018	22:00:00	13/07/2018	01:30:00	shift	4.12
1234	Mat	12/07/2018	13/07/2018	03:00:00	13/07/2018	06:00:00	shift	3.6
1234	Mat	13/07/2018	13/07/2018	22:00:00	14/07/2018	01:30:00	shift	4.08
1234	Mat	13/07/2018	14/07/2018	03:00:00	14/07/2018	06:00:00	shift	3.6
1234	Emma	12/07/2018	12/07/2018	12:30:00	12/07/2018	16:30:00	shift	4.55
1234	Emma	12/07/2018	12/07/2018	18:00:00	12/07/2018	22:00:00	shift	4.28

From here you can see that Ala and Mat are working nightshifts. And Emma is on Dayshift.

This is how my model looks like at the moment:

I did not linked my DimTime table to a Fact Table as I use DAX to creat a matrix. I do have a tendency to over complicate stuff so I hope there is an easier and better solution to my pronlem.

So if my shift is on the same day (StartDate = EndDate) then my measure works as I only comapare time. But it doesn't work with employees who works nightshift as StartTime is greater then EndTime which in that case does not have sense.

Could you please help me? How can i compare Date and Time to show employees who did nightshift.

my measure so far looks like that: 

HeadCount =
SUMX (
    CROSSJOIN (
        SUMMARIZE (
            PunchEvent,
            DimDate[Date],
            PunchEvent[PHP_StartTime],
            PunchEvent[PHP_EndTime]
        ),
        SUMMARIZE ( DimTime, DimTime[Time24] )
    ),
    IF (
        AND (
            ( FORMAT ( DimTime[Time24], "HH:mm:ss" ) )
                >= ( FORMAT ( PunchEvent[PHP_StartTime], "HH:mm:ss" ) ),
            ( FORMAT ( DimTime[Time24], "HH:mm:ss" ) )
                <= ( FORMAT ( PunchEvent[PHP_EndTime], "HH:mm:ss" ) )
        ),
        1,
        0
    )
)

thanks!

[v-yulgu-msft]

Hi @trinitykala,

Which field did you add to matrix "column" to show Hours? Which field did you add t Matirx "rows" to show Date values? Based on my assumption, it's "EventDate" from Fact table, right? If so, I have one concern, for the second row, the PunchWorkedHrs should be calculated on 12/07/2018 or 13/07/2018?

Regards,

Yuliana Gu

[trinitykala]

Hi Yuliana,

Thank you for your reply. Probably would be easier if I could attache a pbix file. 🙂

To show Hours in a column I used: DimTime[Hour24] and for Date Value I used DimDate[Date] which is linked via active relationship with PunchEvents[Event Date], and in active relationships to PHP_StartDate and PHP_EndDate for the shift.

I can see your concern and you are correct it should be PHP_StartDate instead Event Date.

If I change dates I will get something like that:

My biggest issue is to get Orange part working correctly. 

I was thinking to create virtual table where I will split my over midnight shift in to two shifts.

Original shift segment:

EventDate	PHP_StartDate	PHP_StartTime	PHP_EndDate	PHP_EndTime
09-Jul-18	09-Jul-18	21:00:00	10-Jul-18	02:30:00

Split:

EventDate	PHP_StartDate	PHP_StartTime	PHP_EndDate	PHP_EndTime
09-Jul-18	09-Jul-18	21:00:00	10-Jul-18	00:00:00
09-Jul-18	10-Jul	00:00:00	10-Jul-18	02:30:00

But I got stuck on how to insert new row.

I hope that helps 🙂 

[trinitykala]

Does anyone have any idea on how to help me out with this issue, please? 

Any links to examples of time (not date) related articles or videos would be much appriciated. 

How do you, build your models to handle Date and time? 

thanks!