Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
Hi @ftorres ,
Could you have tried to add an index for your data model?
Regards,
Daniel He
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |