Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

How to skip Same dates in Column and Consider Next Max dates in DAX

I need to show the calculation as "If End Time is lesser than Next row  start Time it need to show me the other row which is higher than End Time .. Example - In below screenshot in Second row End Time is 1/15/2024 9:12:00 Am and Next start time is 1/14/2024 6:43:33 Pm .. As the 3rd row start time is lesser than 2nd row End time it show negative values .. Here i need calculation where it should capture 1/18/2024 9:17:37 AM in Second row Next start time ... Thank you sooo much for helping in advance

 

PBCommunity Anonymous @community support v-xiaosun-msft 

 

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    Anonymous You'll need an Index to define "next". Then something along these lines. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Column = 
      VAR __Current = [Value]
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
     ( __Current - __Previous ) * 1.

    • Anonymous's avatar
      Anonymous
      Not applicable

      hi Greg_Deckler  Thanks for your response .. I am getting the below error when i am trying to update the formula 

       

       

       

      Not sure for Value should i consider Index or the Number column.. 

       

      Could you please help me with the exact formula that would be really helpful

       

      Column = 
        VAR __Current = [Value]
        VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

        VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
      RETURN
       ( __Current - __Previous ) * 1.

       

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous 

    Thanks for the solution Greg_Deckler  provided, and i want to offer some more information for you to refer to, based on your description, you should want to get the next start time greater than the current end time if the current end time is greater than the start time of the next row? if you want to achieve this, you can refer to the following solution.

    first,  as Greg_Deckler  mentioned, you need to have an index column, then you can create the measure below.

    MEASURE =
    VAR _nextstarttime =
        CALCULATE (
            MAX ( 'Incident'[Actual Start CST] ),
            ALLSELECTED ( 'Incident' ),
            'Incident'[Index]
                = MAX ( 'Incident'[Index] ) + 1
        )
    VAR _index =
        CALCULATE (
            MIN ( 'Incident'[Index] ),
            ALLSELECTED ( 'Incident' ),
            'Incident'[Actual Start CST] > MAX ( 'Incident'[END Time CST] ),
            'Incident'[Index]
                > MAX ( 'Incident'[Index] ) + 1
        )
    RETURN
        IF (
            MAX ( 'Incident'[END Time CST] ) > _nextstarttime,
            CALCULATE (
                MAX ( 'Incident'[Actual Start CST] ),
                ALLSELECTED ( 'Incident' ),
                'Incident'[Index] = _index
            ),
            _nextstarttime
        )
    

    Ouptut

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi @ v-xinruzhu-msft . Thank you for the response and the data is alsmot matched. But one issue is it should not consider the date if the Actual start CST is same date for the next row.. For example 

      2nd row Actual start CST and 3rd row actual CST is on same date . Hence its should capture for only 2nd row and for 3rd row it should show blank/Zero . The one that is highlighted in Yellow should be in Blank/zero. On 5th row you can see that the dates is falling between 4th row End Time CST . Hence i cant show the duration for the same dates twice . I am Trying to find out the Uptime of the Incidents 

       

       

       

      Regards,

      Manu

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi Anonymous 

        Thanks for your reply and based on your description, please try the following measure.

        MEASURE =
        VAR _nextstarttime =
            CALCULATE (
                MAX ( 'Incident'[Actual Start CST] ),
                ALLSELECTED ( 'Incident' ),
                'Incident'[Index]
                    = MAX ( 'Incident'[Index] ) + 1
            )
        VAR _index =
            CALCULATE (
                MIN ( 'Incident'[Index] ),
                ALLSELECTED ( 'Incident' ),
                'Incident'[Actual Start CST] > MAX ( 'Incident'[END Time CST] ),
                'Incident'[Index]
                    > MAX ( 'Incident'[Index] ) + 1
            )
        RETURN
            IF (
                MAX ( 'Incident'[END Time CST] ) > _nextstarttime,
                CALCULATE (
                    MAX ( 'Incident'[Actual Start CST] ),
                    ALLSELECTED ( 'Incident' ),
                    'Incident'[Index] = _index
                )
            )
        

        Output

        Best Regards!

        Yolo Zhu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.