Forum Discussion

KUMAR_AK's avatar
KUMAR_AK
Icon for Microsoft Employee rankMicrosoft 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...
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • v-janeyg-msft's avatar
    v-janeyg-msft
    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's avatar
KUMAR_AK
Icon for Microsoft Employee rankMicrosoft 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's avatar
v-janeyg-msft
Icon for Community Support rankCommunity 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

  • KUMAR_AK's avatar
    KUMAR_AK
    Icon for Microsoft Employee rankMicrosoft 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 

     



    StartTimeEndTimeIDNameBreakDetailsExpected Output
    10/13/2021 6:55:00 AMnullA-125698LoginNo Breaktime0
    10/13/2021 7:00:00 AM10/13/2021 7:30:00 AMB-125698 IssuesBreaktime0
    10/13/2021 9:00:00 AMnullC-125698StartNo Breaktime0
    10/13/2021 10:00:00 AM10/13/2021 11:00:00 AMD-125698Issuewaiting0
    10/13/2021 11:30:00 AM10/13/2021 11:45:00 AME-125698 IssuesBreaktime15
    10/13/2021 1:00:00 PMnullF-125698XOOJwaiting time0
    10/13/2021 1:10:00 PMnullG-125698EndNo Breaktime0
    10/13/2021 1:45:00 PM10/13/2021 2:40:00 PMH-125698NextBreaktime0
    10/13/2021 3:00:00 PMnullC-125698Startwaiting time0
    10/13/2021 3:15:00 PM10/13/2021 3:30:00 PMI-125698RITNo Breaktime0
    10/13/2021 4:00:00 PM10/13/2021 16:30F-125698InjectBreaktime30
    10/13/2021 5:00:00 PM10/13/2021 5:30:00 PMK-125698RIHwaiting time0
    10/13/2021 6:00:00 PMnullC-125698Millwaiting time0
    10/13/2021 6:30:00 PM10/13/2021 7:00:00 PMM-125698Tagwaiting time0
    10/13/2021 7:30:00 PMnullF-125698EndNo Breaktime0
    10/13/2021 8:00:00 PM10/13/2021 20:30S-125698DownBreaktime0
    10/13/2021 21:00nullt-125698Job TicketNo Breaktime0
         45


    Thanks
    Kumar

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity 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's avatar
        KUMAR_AK
        Icon for Microsoft Employee rankMicrosoft 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_EndtimeidnamebreakdetailsMeasure 2 OutputExpectedOutPut
        10/18/2021 9:00 A-125698MainON00
        10/18/2021 10:10 B-125698Crew ON00
        10/18/2021 9:15 C-125698PipeON0 
        10/18/2021 9:20 D-125698TestON00
        10/18/2021 9:4010/18/2021 10:40E-125698IssuesBreakTime600
        10/18/2021 10:00 F-125698Open ON00
        10/18/2021 10:15 G-125698RIIn00
        10/18/2021 10:3010/18/2021 10:42H-125698MillBreakTime1212
        10/18/2021 10:45 C-125698POIn00
        10/18/2021 11:00 I-125698CloseON00
        10/18/2021 11:05 P-125698NextON00
        10/18/2021 11:10 F-125698MILLON00
        10/18/2021 11:1510/18/2021 11:45Q-125698TAGBreakTime450
        10/18/2021 11:20 R-125698RIGON00
        10/18/2021 11:50 K-125698OpenON00
        10/18/2021 12:00 C-125698RIIn00
        10/18/2021 12:1510/18/2021 12:45M-125698InjectBreaktime3030
        10/18/2021 12:30 F-125698RIIn00
        10/18/2021 13:15 S-125698TagIn 00
        10/18/2021 13:30 t-125698POIn 00
        10/18/2021 13:45 U-125698CloseON00
        10/18/2021 14:00 V-125698DownON00
        10/18/2021 14:3010/18/2021 21:57W-125698Job Ticket ON00
        10/18/2021 14:54 X-125698Main ON00
        10/18/2021 15:3010/18/2021 16:00Y-125698Poor BreakTime300
        10/18/2021 16:55 Z-125698ReviewON00
             17742


        Thanks 
        Kumar