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 StartTimeEndTimeIDNameBreakDetails10/13/2021 6:55:00 AMnullA-125698LoginNo Breaktime10/13/2021 7:00:00 AM10/13/2021 7:30:00 AMB-125698 IssuesBreak10/13/2021 9:00:00 AMnullC-125698StartNo Breaktime10/13/2021 10:00:00 AM10/13/2021 11:00:00 AMD-125698Issuewaiting10/13/2021 11:30:00 AM10/13/2021 11:45:00 AME-125698 IssuesBreaktime10/13/2021 1:00:00 PMnullF-125698XOOJwaiting time10/13/2021 1:10:00 PMnullG-125698EndNo Breaktime10/13/2021 1:45:00 PM10/13/2021 2:40:00 PMH-125698NextNo Breaktime10/13/2021 3:00:00 PMnullC-125698Startwaiting time10/13/2021 3:15:00 PM10/13/2021 3:30:00 PMI-125698RITNo Breaktime10/13/2021 4:00:00 PM10/13/2021 16:30F-125698InjectBreaktime10/13/2021 5:00:00 PM10/13/2021 5:30:00 PMK-125698RIHwaiting time10/13/2021 6:00:00 PMnullC-125698Millwaiting time10/13/2021 6:30:00 PM10/13/2021 7:00:00 PMM-125698Tagwaiting time10/13/2021 7:30:00 PMnullF-125698EndNo Breaktime10/13/2021 8:00:00 PMnullS-125698DownNo Breaktime10/13/2021 21:00nullt-125698Job TicketNo Breaktime2. 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 MINUTESBreakTIME = 45 MINUTESThanks Kumar

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

Forum Discussion

KUMAR_AK

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

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

14 Replies

lbendlin
Super User
4 years ago
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
  Microsoft Employee
  4 years ago
  Hello
  Added sample in table format and expeceted output in description
  
  Thanks
  Kumar
  - lbendlin
    Super User
    4 years ago
    Is there any meaning behind the ID field or is it all for the same person/process?
KUMAR_AK
Microsoft Employee
4 years ago
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
Microsoft Employee
4 years ago
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

v-janeyg-msft

Community Support

4 years ago

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

2021.11.2.pbix56 KB

KUMAR_AK
Microsoft Employee
4 years ago
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
  Community Support
  4 years ago
  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

Forum Discussion

Minus Breaktime Between Start and End Time

14 Replies

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