cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

8 REPLIES 8
Frequent Visitor

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

Community Support

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.

Frequent Visitor

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

Community Support

Hi @manubk

``````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.

Frequent Visitor

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

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

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.

Super User

@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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors