Minus Breaktime Between Start and End Time

[KUMAR_AK]

Hi Everyone,

I was hoping someone could help me with a part of the formula to get me to my end goal.
I have the below dataset

StartTime	EndTime	ID	Name	BreakDetails
10/13/2021 6:55:00 AM	null	A-125698	Login	No Breaktime
10/13/2021 7:00:00 AM	10/13/2021 7:30:00 AM	B-125698	Issues	Break
10/13/2021 9:00:00 AM	null	C-125698	Start	No Breaktime
10/13/2021 10:00:00 AM	10/13/2021 11:00:00 AM	D-125698	Issue	waiting
10/13/2021 11:30:00 AM	10/13/2021 11:45:00 AM	E-125698	Issues	Breaktime
10/13/2021 1:00:00 PM	null	F-125698	XOOJ	waiting time
10/13/2021 1:10:00 PM	null	G-125698	End	No Breaktime
10/13/2021 1:45:00 PM	10/13/2021 2:40:00 PM	H-125698	Next	No Breaktime
10/13/2021 3:00:00 PM	null	C-125698	Start	waiting time
10/13/2021 3:15:00 PM	10/13/2021 3:30:00 PM	I-125698	RIT	No Breaktime
10/13/2021 4:00:00 PM	10/13/2021 16:30	F-125698	Inject	Breaktime
10/13/2021 5:00:00 PM	10/13/2021 5:30:00 PM	K-125698	RIH	waiting time
10/13/2021 6:00:00 PM	null	C-125698	Mill	waiting time
10/13/2021 6:30:00 PM	10/13/2021 7:00:00 PM	M-125698	Tag	waiting time
10/13/2021 7:30:00 PM	null	F-125698	End	No Breaktime
10/13/2021 8:00:00 PM	null	S-125698	Down	No Breaktime
10/13/2021 21:00	null	t-125698	Job Ticket	No Breaktime

2. for breaktime we will have end date for that 

IF there is any break time in between start and end i want to minus that time from start and end time.

Expected Output 

FROM ABOVE TABLE IM LOOKING FOR THE BELOW DATE CALUCATIONS

2.break DURING TIME --- 

OUT OF 250 MINUTES MINUS (10/13/2021 11:30:00 AM - 10/13/2021 11:45:00 AM) = 15 MINUTE

OUT OF 300 MINUTES (3rd OPEN START TIME AND CLOSE TIME ) 90 MINUS (10/13/2021 4:30:00 PM - 10/13/2021 4:30:00 PM) = 30 MINUTES

BreakTIME = 45 MINUTES

Thanks 
Kumar

[v-janeyg-msft]

Hi, @KUMAR_AK 

According to your description, I think you can create two measures to get your desired result.

Like this:

TOTAL POTENTIAL TIME = 
SUMX (
    SUMMARIZE (
        'Table',
        [startdate],
        [enddate],
        [name],
        "a",
            VAR startdate =
                CALCULATE ( MAX ( 'Table'[startdate] ), 'Table'[name] = "start" )
            VAR enddate =
                IF (
                    SELECTEDVALUE ( 'Table'[name] ) = "start",
                    MINX (
                        FILTER (
                            ALL ( 'Table' ),
                            [name] = "end"
                                && [startdate] > SELECTEDVALUE ( 'Table'[startdate] )
                        ),
                        [startdate]
                    )
                )
            RETURN
                DATEDIFF ( SELECTEDVALUE ( 'Table'[startdate] ), enddate, MINUTE )
    ),
    [a]
)

NPT DURING TOTAL POTENTIAL TIME =
SUMX (
    SUMMARIZE (
        'Table',
        [startdate],
        [enddate],
        [name],
        "a",
            VAR minstart =
                CALCULATE (
                    MIN ( 'Table'[startdate] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[name] = "start" )
                )
            VAR maxend =
                CALCULATE (
                    MAX ( 'Table'[startdate] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[name] = "end" )
                )
            VAR maxdate =
                MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        [name] <> "breaktime"
                            && [startdate] < SELECTEDVALUE ( 'Table'[startdate] )
                    ),
                    [startdate]
                )
            VAR statename =
                MAXX ( FILTER ( ALL ( 'Table' ), [startdate] = maxdate ), [name] )
            RETURN
                IF (
                    SELECTEDVALUE ( 'Table'[name] ) = "breaktime"
                        && SELECTEDVALUE ( 'Table'[startdate] ) > minstart
                        && SELECTEDVALUE ( 'Table'[startdate] ) < maxend
                        && statename = "start",
                    DATEDIFF (
                        SELECTEDVALUE ( 'Table'[startdate] ),
                        SELECTEDVALUE ( 'Table'[enddate] ),
                        MINUTE
                    )
                )
    ),
    [a]
)

Below is my sample. Hope it helps.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[KUMAR_AK]

Hi @v-janeyg-msft 
Thanks for the info, it works , just trying to implement the same if Breaktime is in Seperate column like break details 
Calculation is same just encountering some error in  below piece of codefor npt during potentioal time 

VAR maxdate =

MAXX (

FILTER (

ALL ( 'Table' ),

[name] <> "breaktime"

&& [startdate] < SELECTEDVALUE ( 'Table'[startdate] )

),

[startdate]

)

VAR statename =

MAXX ( FILTER ( ALL ( 'Table' ), [startdate] = maxdate ), [name])

If you can elp me in that thats works Please 

Thanks 
Kumar

[v-janeyg-msft]

Hi, @KUMAR_AK 

Real breakdetails is better to write logic. I thought about the previous one for a long while.😅

You can try:

Measure = 
SUMX (
    SUMMARIZE (
        'Table',
        [StartTime],
        [EndTime],
        [Name],
        "a",
                IF (
                    SELECTEDVALUE ( 'Table'[BreakDetails] ) = "Breaktime",
        
                    DATEDIFF (
                        SELECTEDVALUE ( 'Table'[StartTime] ),
                        SELECTEDVALUE ( 'Table'[EndTime] ),
                        MINUTE
                    ),
                    0
                )
    ),
    [a]
)

Best Regards,
Community Support Team _ Janey

[KUMAR_AK]

Hi @v-janeyg-msft 

Really Sorry  on last thing there was a small typo in my screenshot , i have breaktime before start and after end also 
and in between end and start , i want only breaktime in between start and end 

Below is the screen hot for exact Expectedoutput  hilighited in column  last one and red highlited are breaktime before start and after end 

StartTime	EndTime	ID	Name	BreakDetails	Expected Output
10/13/2021 6:55:00 AM	null	A-125698	Login	No Breaktime	0
10/13/2021 7:00:00 AM	10/13/2021 7:30:00 AM	B-125698	Issues	Breaktime	0
10/13/2021 9:00:00 AM	null	C-125698	Start	No Breaktime	0
10/13/2021 10:00:00 AM	10/13/2021 11:00:00 AM	D-125698	Issue	waiting	0
10/13/2021 11:30:00 AM	10/13/2021 11:45:00 AM	E-125698	Issues	Breaktime	15
10/13/2021 1:00:00 PM	null	F-125698	XOOJ	waiting time	0
10/13/2021 1:10:00 PM	null	G-125698	End	No Breaktime	0
10/13/2021 1:45:00 PM	10/13/2021 2:40:00 PM	H-125698	Next	Breaktime	0
10/13/2021 3:00:00 PM	null	C-125698	Start	waiting time	0
10/13/2021 3:15:00 PM	10/13/2021 3:30:00 PM	I-125698	RIT	No Breaktime	0
10/13/2021 4:00:00 PM	10/13/2021 16:30	F-125698	Inject	Breaktime	30
10/13/2021 5:00:00 PM	10/13/2021 5:30:00 PM	K-125698	RIH	waiting time	0
10/13/2021 6:00:00 PM	null	C-125698	Mill	waiting time	0
10/13/2021 6:30:00 PM	10/13/2021 7:00:00 PM	M-125698	Tag	waiting time	0
10/13/2021 7:30:00 PM	null	F-125698	End	No Breaktime	0
10/13/2021 8:00:00 PM	10/13/2021 20:30	S-125698	Down	Breaktime	0
10/13/2021 21:00	null	t-125698	Job Ticket	No Breaktime	0
					45

Thanks
Kumar

[v-janeyg-msft]

@KUMAR_AK 

Try this:

Measure 2 = 
SUMX (
    SUMMARIZE (
        'Table',
        [StartTime],
        [EndTime],
        [Name],
        "a",
            VAR minstart =
                CALCULATE (
                    MIN ( 'Table'[StartTime] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Name] = "Start" )
                )
            VAR maxend =
                CALCULATE (
                    MAX ( 'Table'[StartTime] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Name] = "End" )
                )
            RETURN
                IF (
                    SELECTEDVALUE ( 'Table'[BreakDetails] ) = "Breaktime"
                        && SELECTEDVALUE ( 'Table'[StartTime] ) > minstart
                        && SELECTEDVALUE ( 'Table'[StartTime] ) < maxend
                        &&SELECTEDVALUE('Table'[Name])<>"Next",
                    DATEDIFF (
                        SELECTEDVALUE ( 'Table'[StartTime] ),
                        SELECTEDVALUE ( 'Table'[EndTime] ),
                        MINUTE
                    ),
                    0
                )
    ),
    [a]
)

 Best Regards,
Community Support Team _ Janey

[KUMAR_AK]

Hi @v-janeyg-msft 

Measure 2 Sceaniro is failing for below logic
Just i want to be clear , 
1. I want only breaktime in between open start Time and the close start time 

and this 

2. want to deduct any brektime before open start time ,and after close start time , or in between close start time  and open start time 
Below are the screen shot  this one 

below one 

3. Any BreakTime after first close time and next open time should be deducted , name may be tag or next or anything 

Below is the entire screenshot and data 

starttime	_Endtime	id	name	breakdetails	Measure 2 Output	ExpectedOutPut
10/18/2021 9:00		A-125698	Main	ON	0	0
10/18/2021 10:10		B-125698	Crew	ON	0	0
10/18/2021 9:15		C-125698	Pipe	ON	0
10/18/2021 9:20		D-125698	Test	ON	0	0
10/18/2021 9:40	10/18/2021 10:40	E-125698	Issues	BreakTime	60	0
10/18/2021 10:00		F-125698	Open	ON	0	0
10/18/2021 10:15		G-125698	RI	In	0	0
10/18/2021 10:30	10/18/2021 10:42	H-125698	Mill	BreakTime	12	12
10/18/2021 10:45		C-125698	PO	In	0	0
10/18/2021 11:00		I-125698	Close	ON	0	0
10/18/2021 11:05		P-125698	Next	ON	0	0
10/18/2021 11:10		F-125698	MILL	ON	0	0
10/18/2021 11:15	10/18/2021 11:45	Q-125698	TAG	BreakTime	45	0
10/18/2021 11:20		R-125698	RIG	ON	0	0
10/18/2021 11:50		K-125698	Open	ON	0	0
10/18/2021 12:00		C-125698	RI	In	0	0
10/18/2021 12:15	10/18/2021 12:45	M-125698	Inject	Breaktime	30	30
10/18/2021 12:30		F-125698	RI	In	0	0
10/18/2021 13:15		S-125698	Tag	In	0	0
10/18/2021 13:30		t-125698	PO	In	0	0
10/18/2021 13:45		U-125698	Close	ON	0	0
10/18/2021 14:00		V-125698	Down	ON	0	0
10/18/2021 14:30	10/18/2021 21:57	W-125698	Job Ticket	ON	0	0
10/18/2021 14:54		X-125698	Main	ON	0	0
10/18/2021 15:30	10/18/2021 16:00	Y-125698	Poor	BreakTime	30	0
10/18/2021 16:55		Z-125698	Review	ON	0	0
					177	42

Thanks 
Kumar

[v-janeyg-msft · Accepted solution]

Hi, @KUMAR_AK 

I used other logic to implement it before, so it will be relatively simple

If it must be this logic, Dax is not easy to express. Sometimes the logic of the language is converted into dax, which is not as simple as it seems, it is very complicated.

I hope you provide the sample won't change...😅

Try:

Measure =
IF (
    HASONEVALUE ( 'Table'[starttime] ),
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( ALL ( 'Table' ), [name] = "Open" ),
                [starttime],
                "enddate",
                    MINX (
                        FILTER (
                            ALL ( 'Table' ),
                            [name] = "Close"
                                && [starttime] > EARLIER ( 'Table'[starttime] )
                        ),
                        [starttime]
                    )
            ),
            "breaktime",
                SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        [starttime] > EARLIER ( 'Table'[starttime] )
                            && [starttime] < EARLIER ( [enddate] )
                            && [breakdetails] = "Breaktime"
                            && [starttime] = SELECTEDVALUE ( 'Table'[starttime] )
                    ),
                    DATEDIFF ( [starttime], [_Endtime], MINUTE )
                )
        ),
        [breaktime]
    ),
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( ALL ( 'Table' ), [name] = "Open" ),
                [starttime],
                "enddate",
                    MINX (
                        FILTER (
                            ALL ( 'Table' ),
                            [name] = "Close"
                                && [starttime] > EARLIER ( 'Table'[starttime] )
                        ),
                        [starttime]
                    )
            ),
            "breaktime",
                SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        [starttime] > EARLIER ( 'Table'[starttime] )
                            && [starttime] < EARLIER ( [enddate] )
                            && [breakdetails] = "Breaktime"
                    ),
                    DATEDIFF ( [starttime], [_Endtime], MINUTE )
                )
        ),
        [breaktime]
    )
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[KUMAR_AK]

Hi @v-janeyg-msft 
I this that should work ,also 
Can you please share the Pbix file ?

Thanks
Kumar

[v-janeyg-msft]

Hi, @KUMAR_AK 

I just simply used the enter data to copy your sample data and create the measure, not do other operations.

[KUMAR_AK]

Hi ID is for identifying start and end I'd 's and those start if and end if are unique to identify start or end remaining or random 

[KUMAR_AK]

Hi ID is for identifying start and end I'd 's and those start if and end if are unique to identify start or end remaining or random 

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[KUMAR_AK]

Hello 
Added sample in table format and expeceted output in description

Thanks
Kumar

[lbendlin]

Is there any meaning behind the ID field or is it all for the same person/process?