Forum Discussion
Highlight changed value over time
Hi Anonymous,
First, what's your expected based on this sample data?
If you want to compare the Commitment rate between current week and last week. You can try following calculated column:
Last Week value = IF ( ISBLANK ( LOOKUPVALUE ( 'Table'[Commitment rate], 'Table'[Employee_ID], 'Table'[Employee_ID], 'Table'[t+], 'Table'[t+] - 1 ) ), 'Table'[Commitment rate], LOOKUPVALUE ( 'Table'[Commitment rate], 'Table'[Employee_ID], 'Table'[Employee_ID], 'Table'[t+], 'Table'[t+] - 1 ) )You can check the sample report here: https://1drv.ms/u/s!AlqSnZZUVHmshXQ7rxV2bFmtAamf
Thanks,
Xi Jin.
Hi v-xjiin-msft,
Thank you for your response.
See attached a sample of the data used.
All employees with a commitment rate <= 90 are classified as idle and those >90 are classified as not idle.
Greetings,
Ronald
| Employee_ID | t+ | Commitment rate | Status |
| 1 | 1 | 100 | Not idle |
| 1 | 2 | 100 | Not idle |
| 1 | 3 | 100 | Not idle |
| 1 | 4 | 100 | Not idle |
| 1 | 5 | 100 | Not idle |
| 1 | 6 | 100 | Not idle |
| 1 | 7 | 100 | Not idle |
| 1 | 8 | 100 | Not idle |
| 2 | 1 | 0 | idle |
| 2 | 2 | 0 | idle |
| 2 | 3 | 0 | idle |
| 2 | 4 | 0 | idle |
| 2 | 5 | 0 | idle |
| 2 | 6 | 0 | idle |
| 2 | 7 | 0 | idle |
| 2 | 8 | 0 | idle |
| 3 | 1 | 100 | Not idle |
| 3 | 2 | 100 | Not idle |
| 3 | 3 | 95 | Not idle |
| 3 | 4 | 90 | idle |
| 3 | 5 | 80 | idle |
| 3 | 6 | 80 | idle |
| 3 | 7 | 0 | idle |
| 3 | 8 | 0 | idle |
| 4 | 1 | 100 | Not idle |
| 4 | 2 | 100 | Not idle |
| 4 | 3 | 100 | Not idle |
| 4 | 4 | 100 | Not idle |
| 4 | 5 | 100 | Not idle |
| 4 | 6 | 100 | Not idle |
| 4 | 7 | 100 | Not idle |
| 4 | 8 | 100 | Not idle |
| 5 | 1 | 100 | Not idle |
| 5 | 2 | 100 | Not idle |
| 5 | 3 | 100 | Not idle |
| 5 | 4 | 100 | Not idle |
| 5 | 5 | 100 | Not idle |
| 5 | 6 | 100 | Not idle |
| 5 | 7 | 100 | Not idle |
| 5 | 8 | 100 | Not idle |
| 6 | 1 | 100 | Not idle |
| 6 | 2 | 100 | Not idle |
| 6 | 3 | 100 | Not idle |
| 6 | 4 | 100 | Not idle |
| 6 | 5 | 100 | Not idle |
| 6 | 6 | 100 | Not idle |
| 6 | 7 | 100 | Not idle |
| 6 | 8 | 100 | Not idle |
| 7 | 1 | 90 | idle |
| 7 | 2 | 90 | idle |
| 7 | 3 | 90 | idle |
| 7 | 4 | 90 | idle |
| 7 | 5 | 90 | idle |
| 7 | 6 | 90 | idle |
| 7 | 7 | 90 | idle |
| 7 | 8 | 90 | idle |
| 8 | 1 | 0 | idle |
| 8 | 2 | 0 | idle |
| 8 | 3 | 100 | Not idle |
| 8 | 4 | 100 | Not idle |
| 8 | 5 | 100 | Not idle |
| 8 | 6 | 100 | Not idle |
| 8 | 7 | 100 | Not idle |
| 8 | 8 | 100 | Not idle |
| 9 | 1 | 80 | idle |
| 9 | 2 | 80 | idle |
| 9 | 3 | 80 | idle |
| 9 | 4 | 80 | idle |
| 9 | 5 | 80 | idle |
| 9 | 6 | 100 | Not idle |
| 9 | 7 | 100 | Not idle |
| 9 | 8 | 100 | Not idle |
| 10 | 1 | 100 | Not idle |
| 10 | 2 | 100 | Not idle |
| 10 | 3 | 100 | Not idle |
| 10 | 4 | 100 | Not idle |
| 10 | 5 | 100 | Not idle |
| 10 | 6 | 100 | Not idle |
| 10 | 7 | 100 | Not idle |
| 10 | 8 | 100 | Not idle |
Solution SageHi Anonymous,
First, what's your expected based on this sample data?
If you want to compare the Commitment rate between current week and last week. You can try following calculated column:
Last Week value =
IF (
ISBLANK (
LOOKUPVALUE (
'Table'[Commitment rate],
'Table'[Employee_ID], 'Table'[Employee_ID],
'Table'[t+], 'Table'[t+] - 1
)
),
'Table'[Commitment rate],
LOOKUPVALUE (
'Table'[Commitment rate],
'Table'[Employee_ID], 'Table'[Employee_ID],
'Table'[t+], 'Table'[t+] - 1
)
)
You can check the sample report here: https://1drv.ms/u/s!AlqSnZZUVHmshXQ7rxV2bFmtAamf
Thanks,
Xi Jin.
Hi v-xjiin-msft,
Thank you for your reply.
It was not exactly the answer I was looking for but it did help me to solve the case.
Please find attached a picture of what I was trying to achieve.
Many thanks!
Ronald