Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
manubk
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

 

manubk_0-1712216093567.png

@PBCommunity @community @community support @v-xiaosun-msft 

 

8 REPLIES 8
manubk
Frequent Visitor

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

v-xinruzhu-msft
Community Support
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

vxinruzhumsft_0-1712545119120.png

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.

 

 

 

 

 

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 

 

manubk_0-1712582150596.png

 

 

Regards,

Manu

 

 

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

vxinruzhumsft_0-1712625102938.png

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.

 

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 

 

manubk_3-1712737821363.png

 

manubk_1-1712736958814.pngmanubk_2-1712736990963.png

 

Regards,

Manu B K

 

Greg_Deckler
Super User
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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

manubk_3-1712225731417.png

 

manubk_2-1712225607080.png

 

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.

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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