Forum Discussion

Microsoft Employee

4 years ago

Solved

Minus Breaktime Between Start and End Time

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/202...

4 years ago

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

Microsoft Employee

4 years ago

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

Community Support

4 years ago

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

Microsoft Employee

4 years ago

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

Community Support

4 years ago

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
Microsoft Employee
4 years ago
Hi v-janeyg-msft
I this that should work ,also
Can you please share the Pbix file ?

Thanks
Kumar

Forum Discussion

Minus Breaktime Between Start and End Time

Related content

Date measure minus one year

Power Query Todays date minus 1 day

Categorise Plus or Minus %

row minus previous row

LastDate minus 1