Returning a Conditional String From Next Row

[aJamie]

Hi,

I'm trying to add a column to the table below to calculate the 'Next ID', based on 'Last ID' and where 'Mins' is not 0, with a reset at each Product change.

Any help with this would be appreciated..

Current:

Product	Last ID	Mins
Prod1	105	10
Prod1	205	20
Prod1	305	0
Prod1	405	15
Prod1	505	0
Prod1	605	20
Prod2	105	10
Prod2	205	0
Prod2	305	0
Prod2	405	30
Prod2	505	0
Prod2	605	20
Prod3	105	10
Prod3	205	0
Prod3	305	0
Prod3	405	25
Prod3	505	10
Prod3	605	0

Expected Output:

Product	Last ID	Mins		Next ID
Prod1	105	10		205
Prod1	205	20		405
Prod1	305	0		-
Prod1	405	15		605
Prod1	505	0		-
Prod1	605	20		-
Prod2	105	10		405
Prod2	205	0		-
Prod2	305	0		-
Prod2	405	30		605
Prod2	505	0		-
Prod2	605	20		-
Prod3	105	10		405
Prod3	205	0		-
Prod3	305	0		-
Prod3	405	25		505
Prod3	505	10		-
Prod3	605	0		-

Thanks,

Jamie.

[aJamie]

Amazing! ..thanks so much for your help. Perfect solution.

[Tahreem24]

@aJamie  Try this: 

* Make little tweak in @amitchandak DAX like below

Column 2 =

VAR a_ = MINX(FILTER(MyTable, MyTable[Product] =EARLIER(MyTable[Product]) && MyTable[Last ID]>EARLIER(MyTable[Last ID]) && MyTable[Mins] <> 0 ), MyTable[Last ID])

RETURN IF(MyTable[Mins]=0 && MIN(MyTable[Product])<MAX(MyTable[Product]),0,a_)

Then Create a measure like below:

Measure = IF(MAX(MyTable[Column 2])=0,"-",MIN(MyTable[Column 2]))

[aJamie]

Thanks @Tahreem24  - an excellent suggestion..

[amitchandak · Accepted solution]

@aJamie , Create a new column like

minx(filter(Table, [Product] =earlier([Product]) && [Last ID] >earlier([Last ID]) && [Mins] <> 0 ), [Last ID])