Datediff or sumx only in my fiscal time/shift time

[Anonymous]

Ok Guys a quick "business case":
I have my start time and my end time, using datediff i got the elapsed time, but, i dont want to consider the sunday and the time that has passed outside the workers shift.
Lets supose my shift start at 8 am and ends at 2 pm.
I've started a process at 1:30 pm and i didnt managed to finish my job before 2 pm, i dont what my elapsed time to be 18.5 hours, i want the sum or datediff to consider only my "Fiscal time" or "shift time". 

[v-yingjl]

Hi @Anonymous ,

Based on your description, not certain what is your expected output. Could you please consider sharing a sample file without any sesentive information for further discussion?

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl , 
Our factory works 2 shifts: 
6am to 11 am - lunch - 1pm to 3pm
Then
5pm to 10 pm - diner - 11pm - 2am.

Lets supose we work sending messages...
The way we work now if we send a message at 1 am and we dont manage to answare the message till 2am, when the person of the second shift come to the factory, the Elapsed time will be 4h +. And i want this to considere only the time my works are at the factory. 

Now my report shows me something like this

and i dont want the time spent to includ the time that my workers is not working, something like this, not including, lunch, diner and sundays. 

I need this because our report show us the amount of time that or items is in process. 
If or item was triggered on saturday and not fully processed till the end of that day, when monday comes, it will show us that has already more thant 24h that it was triggered, eventhough it doesnt shows the reality. 

[v-yingjl · Accepted solution]

Hi @Anonymous ,

It will look like complex becacuse we need to define the 'working time' manually and consider whether it needs to calculate across days.

Basically the column will be like this based on your picture data to get the expected output, if there are more data, needs to write more conditions which are simliar with that in formulas:

Time spend =
VAR _timestart =
    TIME ( HOUR ( 'Table'[Time start] ), MINUTE ( 'Table'[Time start] ), SECOND ( 'Table'[Time start] ) )
VAR _timeend =
    TIME ( HOUR ( 'Table'[Time end] ), MINUTE ( 'Table'[Time end] ), SECOND ( 'Table'[Time end] ) )
VAR minlun1 =
    TIME ( 6, 0, 0 )
VAR maxlun1 =
    TIME ( 11, 0, 0 )
VAR minlun2 =
    TIME ( 13, 0, 0 )
VAR maxlun2 =
    TIME ( 15, 0, 0 )
VAR mindin1 =
    TIME ( 17, 0, 0 )
VAR maxdin1 =
    TIME ( 22, 0, 0 )
VAR mindin2 =
    TIME ( 23, 0, 0 )
VAR maxdin2 =
    TIME ( 2, 0, 0 )
VAR x =
    TIME ( 24, 0, 0 )
VAR y =
    TIME ( 0, 0, 0 )
RETURN
    SWITCH (
        TRUE (),
        DATEDIFF ( 'Table'[Time start], 'Table'[Time end], DAY ) = 0,
            IF (
                _timestart >= minlun1
                    && _timeend >= maxlun1
                    && _timeend >= minlun2
                    && _timeend <= maxlun2,
                DATEDIFF ( _timestart, maxlun1, HOUR ) + DATEDIFF ( minlun2, _timeend, HOUR ) + 1,
                IF (
                    _timestart <= maxdin2
                        && _timeend >= minlun1
                        && _timeend <= maxlun1,
                    DATEDIFF ( _timestart, maxdin2, HOUR ) + DATEDIFF ( minlun1, _timeend, HOUR ) + 1
                )
            ),
        DATEDIFF ( 'Table'[Time start], 'Table'[Time end], DAY ) = 1,
            IF (
                _timestart >= mindin1
                    && _timestart <= maxdin1
                    && _timeend <= maxdin2,
                DATEDIFF ( _timestart, maxdin1, HOUR )
                    + DATEDIFF ( mindin2, x + 1, HOUR )
                    + DATEDIFF ( y, _timeend, HOUR ) + 1,
                IF (
                    _timestart >= minlun2
                        && _timestart <= maxlun2
                        && _timeend >= maxlun1
                        && _timeend <= minlun2,
                    DATEDIFF ( _timestart, maxlun2, HOUR ) + DATEDIFF ( mindin1, maxdin1, HOUR )
                        + DATEDIFF ( mindin2, x + 1, HOUR )
                        + DATEDIFF ( y, maxdin2, HOUR )
                        + DATEDIFF ( minlun1, maxlun1, HOUR )
                        + DATEDIFF ( minlun2, _timeend, HOUR ) + 1
                )
            )
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yingjl , i'm trying to use this code u sent me, but when i try to use it at my database it doesnt work, i thought i was doing something wrong, so i've downloaded the pbix file you sent me and changed the values of the "end time" column to "now()", and it stoped to shows the result, this code only works if all my process is within 1 month? Or it's suposed to work between anytime? 

[Anonymous]

This was amazing, tnx for the help. ^^

[Anonymous]

Add a calendar. Count the fiscal days. Subtract 1 from number of fiscal days

Calculate starttime till end of schedule for day 1

Calulate start of schedule till endtime

Multiply number of days in forst step by number of hours in schedule

So start on Thursday 13:00

End on Monday 14:30

Schedule daily (not on Sat or Sun) from 9:00 till 15:00 (=6 hours)

Count of working days (datdiff -== 2) - 1 = 1

Hours on first day = 13:00 till 15:00 = 2

Hours on last day = 9:00 till 14:30 = 5.5

Total time = 2 + 5.5 + 1*6 = 13.5 hours