topic Re: How to skip Same dates in Column and Consider Next Max dates in DAX in Desktop

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

Anonymous — Thu, 04 Apr 2024 07:40:30 GMT

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

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

Greg_Deckler — Thu, 04 Apr 2024 09:21:36 GMT

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.

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

Anonymous — Thu, 04 Apr 2024 10:15:39 GMT

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.

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

Greg_Deckler — Thu, 04 Apr 2024 10:19:15 GMT

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

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

Anonymous — Mon, 08 Apr 2024 03:01:06 GMT

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.

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

Anonymous — Mon, 08 Apr 2024 13:22:22 GMT

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

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

Anonymous — Tue, 09 Apr 2024 01:12:26 GMT

Hi Anonymous

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

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.

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

Anonymous — Wed, 10 Apr 2024 08:31:10 GMT

Hi Anonymous 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

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

Anonymous — Fri, 12 Apr 2024 12:44:56 GMT

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