Highlight changed value over time

[Anonymous]

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. 

[v-xjiin-msft]

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.

[Anonymous]

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

[v-xjiin-msft · Accepted solution]

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.

[Anonymous]

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