Forum Discussion
filter context different
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?
3 Replies
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
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.
