Forum Discussion
Highlight changed value over time
Hi All,
I was wondering if someone could help me out.
We are forecasting our employee schedule for +8 weeks 'Forecast'[t+].
We change the record type from Employee to Idle if someone's commitment rate drops below 90%.
Thus we can count the number of employee's and idle 'timer's' and plot them on the 8+ week forecast.
Now we want to be able to check who's joining the idle timers and who is leaving (based on employee ID).
Where 1 stands for Employee and 2 stands for Idle. It would be great to see the difference per week with the corresponding ID's.
If have checked a lot of forums and blog, but I can't figure out a good solution.
Many Thanks in Advance.
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.
4 Replies
Hi Anonymous,
Please kindly share us some sample data which we can copy and paste directly and its corresponding expected result. So that we can get a right direction and provide some proper suggestion.
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 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.