Forum Discussion
Reference Another Row for Future Assignment
Hi, dacosta
You can create the following Calculated columns to get the result you want.
Column 1
Rank by Person name =
RANKX (
FILTER ( ALL ( Assignments ), [person_name] = EARLIER ( [person_name] ) ),
[Assignment Start Day],
,
ASC,
DENSE
)
Column 2
Future Assignment Name =
CALCULATE (
MAX ( 'Assignments'[Assignment Name] ),
FILTER (
ALL ( Assignments ),
[Rank by Person name]
= EARLIER ( [Rank by Person name] ) + 1
&& [person_name] = EARLIER ( [person_name] )
)
)
Column 3
CALCULATE (
MAX ( 'Assignments'[Assignment Start Day] ),
FILTER (
ALL ( Assignments ),
[Rank by Person name]
= EARLIER ( [Rank by Person name] ) + 1
&& [person_name] = EARLIER ( [person_name] )
)
)
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solutions to help the other members find it more quickly.
First off, thank you! This worked for instances where Assignment[person_name] occurs on (2) rows (where a person has 2 assignments). I did have to alter the calculation by removing "<=" and replacing with "=" for the earlier function, after doing this it worked.
AND (
Assignments[person_name] = EARLIER ( Assignments[person_name] ),
Assignments[Next Assignment] = 1
)
But this column calculation is not working with people that have (3) rows/assignments, a current assignment, a next assignment, and another next assignment. See example below:
Hi, dacosta
You can create the following Calculated columns to get the result you want.
Column 1
Rank by Person name =
RANKX (
FILTER ( ALL ( Assignments ), [person_name] = EARLIER ( [person_name] ) ),
[Assignment Start Day],
,
ASC,
DENSE
)
Column 2
Future Assignment Name =
CALCULATE (
MAX ( 'Assignments'[Assignment Name] ),
FILTER (
ALL ( Assignments ),
[Rank by Person name]
= EARLIER ( [Rank by Person name] ) + 1
&& [person_name] = EARLIER ( [person_name] )
)
)
Column 3
CALCULATE (
MAX ( 'Assignments'[Assignment Start Day] ),
FILTER (
ALL ( Assignments ),
[Rank by Person name]
= EARLIER ( [Rank by Person name] ) + 1
&& [person_name] = EARLIER ( [person_name] )
)
)
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
If this post helps, then please consider Accept it as the solutions to help the other members find it more quickly.
This worked perfectly, thanks for the help.