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

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity 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

  • 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