Count of total shift

[Kaalbartje2]

Hi,

I have a datamodel with details when employ's are working.

A column with start (date and time) and end. A shift is mostly build of 3 rows

1. Working part
2. Break
3. Working part.

I want to make something that I can count the shifts. Not the detail information but only the shifts. Sometimes it is enough tot set the values tot Unique but that doesnt work for a drill table. For the day overview it shows the correct value but when you zoom out tot the month (or week) overview it keeps counting the unique values and not the sum of total shifts.

So I have columns:

Start;End;employee number; department; shift code; activity type.

The last column is an code which says (with a number) what kind of activty it is (break = 4, working is 53 etc)

I hope it it's clear enough

[amitchandak]

@Kaalbartje2 , Now very clear

Count of shift can be

Countrows(Summarize(Table, Table[Start], Table[End]))

or

Countrows(Summarize(filter(Table, Table[ activity type] <>  4) , Table[Start], Table[End]))

or

Countrows(Summarize(Table, Table[Start], Table[End], Table[Employee Number]))

[Kaalbartje2]

Hi @amitchandak ,

So finaly had some time to play with your codes.

Code: Countrows(Summarize(Table, Table[Date], Table[Employee Number]))

gives almost the correct number of employees.

The problem what I encounter is that we are working with nighshifts. The start on the fisrt day at 23:30 and end at day 2 at 07:00. Officialy they have a break. Let say around 02:00 till 02:30. And at 02:30 the second shift begins.

When I use this code it counts the correct employees (start time 23:00 or 23:30) but also the employees who are also working the day before.

I can filter that times in PowerBI but I was curious how it is possible to do this in a code.

[Kaalbartje2]

Hi,

Tnx for the help. I'm trying what works best for me. I'll come back to it.