filter context different

[ftorres]

Someone could help me with this query. I need to create a measure that work as "Sum Years" Column. That is accumulating the years but with one condition, the date of joining has to be the date of leaving on line above + 1. Any idea how I could work with this?

[v-danhe-msft]

Hi @ftorres ,

Based on my test, you could refer to below formulas:

b = LOOKUPVALUE(Table1[Date of Joining],'Table1'[Date of Joining],'Table1'[Date of Leaving]+1)

Var = var a = LOOKUPVALUE(Table1[Date of Joining],Table1[b],[Date of Joining])
return IF(a=BLANK()&&'Table1'[b]=BLANK(),BLANK(),[Date of Joining])

SUM Year = IF(ISBLANK('Table1'[Var]),[Years],CALCULATE(SUM(Table1[Years]),FILTER('Table1','Table1'[Var]<=EARLIER(Table1[Var])&&'Table1'[Var]<>BLANK())))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[ftorres]

Hi @v-danhe-msft,

Thanks for the help, but I got a problem when I try to get the var column. My sample is simple. when I apply that in my table it returns a error that "a table of multiple values was supplied where a single value was expected." The ResID in the sample is only one employee, there are loads in the database.  

[v-danhe-msft]

Hi @ftorres ,

Could you have tried to add an index for your data model?

Regards,

Daniel He