Create change column based on value & ranked column

[Anonymous]

Hi

I am trying to recreate the table below, I have already created a Rank column linked to User ID & now need to create the change column indicating if the Bonus column has increased or decreased for each employee: 

Rank	User ID	Bonus	Change
1	12345	8
2	54321	10
2	54321	12	Increase
2	54321	13	Increase
3	12468	6
3	12468	10	Increase
3	12468	8	Decrease

Thanks,

Katie

[parry2k]

@Anonymous one column seems to be missing from table is how to order under each id which value should come first, is there any date column that tells the when that bonus happened.

[Anonymous]

Hi Parry, 

Thanks for the quick response - yes I do have an 'Event Date' field that can be used

[parry2k]

@Anonymous yes that would be required, can you repost your data example with event date column

[Anonymous]

Hi Perry, I've updated the data with the event date:

I should have mentioned that there will be rows with the same bonus value for the same User ID but a different event date - for these the status field should be blank like the below: 

Rank	User ID	Event Date	Bonus	Change
1	12345	01/01/2019	8
1	12345	31/01/2019	8
1	12345	01/02/2019	8
1	12345	01/05/2019	9	Increase
1	12345	01/08/2019	9
1	12345	01/10/2019	9
2	54321	01/02/2019	10
2	54321	01/03/2019	10
2	54321	01/05/2019	10
2	54321	01/08/2019	12	Increase
2	54321	01/12/2019	13	Increase
3	12468	01/05/2019	6
3	12468	01/08/2019	10	Increase
3	12468	01/10/2019	8	Decrease
3	12468	01/12/2019	8
3	12468	01/01/2020	8

[Anonymous]

Hi @Anonymous,

Can you please share a sample pbix file for test? It seems like your table contains duplicate records so that it return multiple records that can't be compared with normal operators.

Notice: remove or do mask on sensitvie data.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Parry, 

Sure no problem - please see the below sample data:

Rank	User ID	Event Date	Bonus	Change
1	12345	01/01/2019	8
2	54321	01/02/2019	10
2	54321	01/08/2019	12	Increase
2	54321	01/12/2019	13	Increase
3	12468	01/05/2019	6
3	12468	01/08/2019	10	Increase
3	12468	01/10/2019	8	Decrease

[Anonymous]

HI @Anonymous,

You can try to use below calculate column formula if it meets for your requirement:

Status =
VAR prevDate =
    CALCULATE (
        MAX ( Table[Event Date] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Rank] = EARLIER ( Table[Rank] )
                && [Event Date] < EARLIER ( Table[Event Date] )
        )
    )
VAR prevBonus =
    LOOKUPVALUE (
        Table[Bonus],
        Table[Rank], [Rank],
        Table[Event Date], prevDate
    )
VAR diff = [Bonus] - prevBonus
RETURN
    IF (
        prevDate <> BLANK (),
        IF ( diff > 0, "Increase", IF ( diff < 0, "Decrease" ) )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for the reply, I am getting the error "A table of multiple values was supplied where a single value was expected.", I tried replacing the MAX with FIRSTNONBLANK & VALUES and each of these functions are returning results but they are not correct i.e the increase, decrease are not labelling accurately.

I should have mentioned that there will be rows with the same bonus value for the same User ID but a different event date - for these the status field should be blank like the below: 

Rank	User ID	Event Date	Bonus	Change
1	12345	01/01/2019	8
1	12345	31/01/2019	8
1	12345	01/02/2019	8
1	12345	01/05/2019	9	Increase
1	12345	01/08/2019	9
1	12345	01/10/2019	9
2	54321	01/02/2019	10
2	54321	01/03/2019	10
2	54321	01/05/2019	10
2	54321	01/08/2019	12	Increase
2	54321	01/12/2019	13	Increase
3	12468	01/05/2019	6
3	12468	01/08/2019	10	Increase
3	12468	01/10/2019	8	Decrease
3	12468	01/12/2019	8
3	12468	01/01/2020	8