DAX Help - Need to find a previous row value, following a criteria

[Anonymous]

I need help in two  DAX formulas that would bring a previous row value for a certain column based on a filter criteria. The formula would be for PrevFinish =  ................................... It would consider and Repeat the Finish_Downtime for the first Index column [ID], obeying the criteria that it belongs to the same MACHINE_ID. It could also be solved if we have a formula for the Rows_Above = .................... What is the ID difference between the current row ID and the first Row above that also belongs to the same Machine_ID ID MACHINE_ID Finish_Dowtime PrevFinish Rows_Above 2890 1 5/27/2021 10:54 5/27/2021 6:28   2891 2 5/27/2021 9:59 5/27/2021 10:42   2892 2 5/27/2021 10:49 5/27/2021 9:59     -1 2893 2 5/27/2021 11:04 5/27/2021 10:49     -1 2894 1 5/27/2021 11:23 5/27/2021 10:54     -4 2895 2 5/27/2021 11:43 5/27/2021 11:04     -2 2896 2 5/27/2021 12:39 5/27/2021 11:43     -1 2897 1 5/27/2021 14:30 5/27/2021 11:23     -3 2899 2 5/27/2021 14:25 5/27/2021 12:39     -2 Thanks for any help.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below for creating new measures.

Prev Finish Measure =
VAR rankbyfinishtime =
RANKX (
ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),
CALCULATE ( MAX ( 'Table'[Finish_Dowtime] ) ),
,
DESC
)
VAR prevtime =
CALCULATE (
MAX ( 'Table'[Finish_Dowtime] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),
RANKX (
ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),
CALCULATE ( MAX ( 'Table'[Finish_Dowtime] ) ),
,
DESC
) > rankbyfinishtime
)
)
RETURN
IF ( ISFILTERED ( 'Table'[ID] ), prevtime )

Rows Above Measure =

VAR currentID =

MAX ( 'Table'[ID] )

VAR rankbyfinishtime =

RANKX (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

CALCULATE ( MAX ( 'Table'[Finish_Dowtime] ) ),

,

DESC

)

VAR prevID =

CALCULATE (

MAX ( 'Table'[ID] ),

FILTER (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

RANKX (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

CALCULATE ( MAX ( 'Table'[Finish_Dowtime] ) ),

,

DESC

) > rankbyfinishtime

)

)

RETURN

IF ( ISFILTERED ( 'Table'[ID] ) && prevID > 0, prevID - currentID, "" )

https://www.dropbox.com/s/sqai7p5etafwysg/elmorlc.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous · Accepted solution]

The solution works Jihwan, but they come as a Measure - what should I change in those same DAX formulas for them to work as a new column and not a Measure?

regards,

Leonardo

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below for creating new measures.

Prev Finish Measure =
VAR rankbyfinishtime =
RANKX (
ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),
CALCULATE ( MAX ( 'Table'[Finish_Dowtime] ) ),
,
DESC
)
VAR prevtime =
CALCULATE (
MAX ( 'Table'[Finish_Dowtime] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),
RANKX (
ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),
CALCULATE ( MAX ( 'Table'[Finish_Dowtime] ) ),
,
DESC
) > rankbyfinishtime
)
)
RETURN
IF ( ISFILTERED ( 'Table'[ID] ), prevtime )

Rows Above Measure =

VAR currentID =

MAX ( 'Table'[ID] )

VAR rankbyfinishtime =

RANKX (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

CALCULATE ( MAX ( 'Table'[Finish_Dowtime] ) ),

,

DESC

)

VAR prevID =

CALCULATE (

MAX ( 'Table'[ID] ),

FILTER (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

RANKX (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

CALCULATE ( MAX ( 'Table'[Finish_Dowtime] ) ),

,

DESC

) > rankbyfinishtime

)

)

RETURN

IF ( ISFILTERED ( 'Table'[ID] ) && prevID > 0, prevID - currentID, "" )

https://www.dropbox.com/s/sqai7p5etafwysg/elmorlc.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous · Accepted solution]

The solution works Jihwan, but they come as a Measure - what should I change in those same DAX formulas for them to work as a new column and not a Measure?

regards,

Leonardo

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below.

PrevFinish CC =

VAR currentfinishtime = 'Table'[Finish_Dowtime]

VAR prevfinishtime =

CALCULATE (

MAX ( 'Table'[Finish_Dowtime] ),

FILTER (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

'Table'[Finish_Dowtime] < currentfinishtime

)

)

RETURN

prevfinishtime

Rows_Above CC =

VAR currentfinishtime = 'Table'[Finish_Dowtime]

VAR prevfinishtime =

CALCULATE (

MAX ( 'Table'[Finish_Dowtime] ),

FILTER (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

'Table'[Finish_Dowtime] < currentfinishtime

)

)

VAR prevID =

CALCULATE (

MAX ( 'Table'[ID] ),

FILTER (

ALLEXCEPT ( 'Table', 'Table'[MACHINE_ID] ),

'Table'[Finish_Dowtime] = prevfinishtime

)

)

RETURN

IF ( NOT ISBLANK ( 'Table'[PrevFinish CC] ), prevID - 'Table'[ID] )

https://www.dropbox.com/s/sqai7p5etafwysg/elmorlc.pbix?dl=0 

[Anonymous]

Jihwan,
It worked well, most of my parameters are in Columns and formulas with Measures and Columns can always be trick.

Have a good week,

Leonardo