DAX measure: get next value on current row

[Benito_BI]

Hello,

I would like to get the categoryid of the next value based on the "End"date and "start"date.

See below the result category is the output that i need:

id	groupid	categoryid	Product	start	end	qty	Result categoryid
12	A	1	test	1-1-2019	1-2-2019	1	2
13	A	2	test2	1-2-2019	2-5-2019	1	4
14	A	2	test2	1-3-2019	2-5-2019	1	4
4	B	1	test18	1-5-2019		1
5	C	4	test3	2-5-2019		2

i have used the following measure:

result_category =

VAR groups = SELECTEDVALUE(table[Groupid])

VAR enddate = SELECTEDVALUE(table[End])

VAR startdate = SELECTEDVALUE(table[Start])

VAR idInitial = SELECTEDVALUE(table[Id])

VAR new_date = CALCULATE(MINX(table,table[Start]),ALLSELECTED(table),table[Start]>=enddate,table[groupid]=groups)

VAR new_id = CALCULATE(MINX(table,table[Id]),ALLSELECTED(table),table[start]=new_date,table[groupid]=groups)
RETURN
CALCULATE(SELECTEDVALUE(table[categoryid]),ALLSELECTED(table),table[id]=new_id

But this isnt working for me.

Could somebody point me in the right direction?

Kind regards,

[Benito_BI]

Hello,

I would like to get the categoryid of the next value based on the "End"date and "start"date.

See below the result category is the output that i need:

id	groupid	categoryid	Product	start	end	qty	Result categoryid
12	A	1	test	1-1-2019	1-2-2019	1	2
13	A	2	test2	1-2-2019	2-5-2019	1	4
14	A	2	test2	1-3-2019	2-5-2019	1	4
4	B	1	test18	1-5-2019		1
5	C	4	test3	2-5-2019		2

i have used the following measure:

result_category =

VAR groups = SELECTEDVALUE(table[Groupid])

VAR enddate = SELECTEDVALUE(table[End])

VAR startdate = SELECTEDVALUE(table[Start])

VAR idInitial = SELECTEDVALUE(table[Id])

VAR new_date = CALCULATE(MINX(table,table[Start]),ALLSELECTED(table),table[Start]>=enddate,table[groupid]=groups)

VAR new_id = CALCULATE(MINX(table,table[Id]),ALLSELECTED(table),table[start]=new_date,table[groupid]=groups)
RETURN
CALCULATE(SELECTEDVALUE(table[categoryid]),ALLSELECTED(table),table[id]=new_id

But this isnt working for me.

Could somebody point me in the right direction?

Kind regards,

[Benito_BI]

I have found a way, but it only works on a small dataset. When i try it on a large dataset i get a memory error.

Does somebody have a idea how i can decrease the load so i wouldnt get a memory error?

result_categoryid = 
VAR v_end = SELECTEDVALUE(Table[end])
VAR v_group = SELECTEDVALUE(Table[groupid])
VAR v_nextstart = CALCULATE(MINX(Table,Table[start]),ALLSELECTED(Table),DATESBETWEEN(Table[start],v_end,EDATE(v_end,1)),Table[groupid]=v_group)
VAR v_id = CALCULATE(MINX(Table,Table[id]),ALLSESLECTED(Table),Table[start]=v_nextstart,Table[groupid]=v_group)
VAR v_category = CALCULATE(SELECTEDVALUE(Table[categoryid]),ALLSELECTED(Table),Table[id]=v_id)
RETURN
IF(SELECTEDVALUE(Table[end])=BLANK(),BLANK(),IF(SELECTEDVALUE(Table[id])=v_id,BLANK(),v_category))

Kind regards

[Anonymous]

Hi,

Have you tried using Lookupvalue. Basically you will need to Create a new column with the below DAX 

LOOKUPVALUE(Table[Category_ID],Table[End_Date],Table[Start_Date])

[Benito_BI]

Hi,

I dont want to use a calculated column, i would like to use it in a measure.

And the lookupvalue also isnt working, this because the startdate of the next value could be later/higher then the rows enddate.

Kind regards,