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

[manubk]

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 

[manubk]

Hi @v-xinruzhu-msft Any update on the above mentioned Query.

[v-xinruzhu-msft]

Hi @manubk 

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.

[manubk]

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

[v-xinruzhu-msft]

Hi @manubk 

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.

[manubk]

Hi @v-xinruzhu-msft  Thanks for your reply . I see there are many blanks on the dates (Measure) as the dates are not capturing correctly. Below is the screen shot of the same 

Regards,

Manu B K

[Greg_Deckler]

@manubk 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/3395....
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.

[manubk]

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.

[Greg_Deckler]

@manubk To get you a specific answer, I would need sample data to create the scenario (see below). As for the current error you are receiving, you need to replace [Date] with either [Actual Start CST] or [End Time CST] or [Next Start Time] depending on your need. Has to be an actual column in your table.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.