cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Calculate Totals Correctly for Shift Time Schedules & Count those with only "1"

Hello community,

I have the following formula that works quite nicely that sums up the number of shifts worked in a day over a 15 minute pattern.

It relates to a time table that is not joined in the model and sums up the number of shifts between a start date time and end date time, to understand resourcing levels every 15 minutes.

Num of Shifts =
VAR mintime =
MIN ( TimeTable[Time15] )
VAR maxtime =
MAX ( TimeTable[Time15] )
VAR maxstartdate = max(Shifts[SharedStartDate])
VAR minstartdate = min(Shifts[SharedStartDate])
VAR shift = CALCULATE (
sum(Shifts[Shift]),
Shifts[SharedStartTime] <= maxtime,
Shifts[SharedEndTime] >= mintime,
Shifts[SharedStartDate]>=minstartdate,
Shifts[SharedStartDate]<=maxstartdate
)
RETURN
IF(shift=blank(),0,shift)

Question 1)
The totals seem to represent the number fo NON Zero , rather than the sum of that row. i.e ther are 96 time slots in a day and 78 timeslots used on the 16th November.

I have had a go with "hasone" value and "sumx" but I cannot seem to get the correct total neeeded.

Question 2)
I want to be able to also count and categorise by day the Number of 15 minutes time value of 1.  ie. if there are 96 time slots and 5 of them only have a value = 1

kind regards

1 ACCEPTED SOLUTION
Community Support

Hi, @Anonymous

It’s my pleasure to answer for you.

According to your description,I think you can create a measure.

Like this:

``````Num of Shifts =
VAR mintime =
MIN ( TimeTable[Time15] )
VAR maxtime =
MAX ( TimeTable[Time15] )
VAR maxstartdate =
MAX ( Shifts[SharedStartDate] )
VAR minstartdate =
MIN ( Shifts[SharedStartDate] )
RETURN
shift
= CALCULATE (
SUM ( Shifts[Shift] ) + 0,
Shifts[SharedStartTime] <= maxtime,
Shifts[SharedEndTime] >= mintime,
Shifts[SharedStartDate] >= minstartdate,
Shifts[SharedStartDate] <= maxstartdate
)``````
``````Num of Shift1 =
VAR mintime =
MIN ( TimeTable[Time15] )
VAR maxtime =
MAX ( TimeTable[Time15] )
VAR maxstartdate =
MAX ( Shifts[SharedStartDate] )
VAR minstartdate =
MIN ( Shifts[SharedStartDate] )
VAR shift =
CALCULATE (
SUM ( Shifts[Shift] ) + 0,
Shifts[SharedStartTime] <= maxtime,
Shifts[SharedEndTime] >= mintime,
Shifts[SharedStartDate] >= minstartdate,
Shifts[SharedStartDate] <= maxstartdate
)
RETURN
CALCULATE ( COUNTROWS ( shifts ), shift = "1" )``````

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi, @Anonymous

It’s my pleasure to answer for you.

According to your description,I think you can create a measure.

Like this:

``````Num of Shifts =
VAR mintime =
MIN ( TimeTable[Time15] )
VAR maxtime =
MAX ( TimeTable[Time15] )
VAR maxstartdate =
MAX ( Shifts[SharedStartDate] )
VAR minstartdate =
MIN ( Shifts[SharedStartDate] )
RETURN
shift
= CALCULATE (
SUM ( Shifts[Shift] ) + 0,
Shifts[SharedStartTime] <= maxtime,
Shifts[SharedEndTime] >= mintime,
Shifts[SharedStartDate] >= minstartdate,
Shifts[SharedStartDate] <= maxstartdate
)``````
``````Num of Shift1 =
VAR mintime =
MIN ( TimeTable[Time15] )
VAR maxtime =
MAX ( TimeTable[Time15] )
VAR maxstartdate =
MAX ( Shifts[SharedStartDate] )
VAR minstartdate =
MIN ( Shifts[SharedStartDate] )
VAR shift =
CALCULATE (
SUM ( Shifts[Shift] ) + 0,
Shifts[SharedStartTime] <= maxtime,
Shifts[SharedEndTime] >= mintime,
Shifts[SharedStartDate] >= minstartdate,
Shifts[SharedStartDate] <= maxstartdate
)
RETURN
CALCULATE ( COUNTROWS ( shifts ), shift = "1" )``````

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors