Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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)
 
Capture.PNG
 
 
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
v-janeyg-msft
Community Support
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" )

If it doesn’t solve your problem, please feel free to ask me.

 

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.

View solution in original post

1 REPLY 1
v-janeyg-msft
Community Support
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" )

If it doesn’t solve your problem, please feel free to ask me.

 

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.

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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