Identify changes in data over previous month for both Text and numeric fields

[PreetMDX]

Hi everyone, I have a scenario as follows:

I have an Id and date field and other columns to be compared with the previous month's data.

For example an ID i.e 101 Manager name or salary changes in the current month, the latest should be visible and it should be highlighted else the previous month's value should be there in a table to identify that this field value is different from the previous month.

ID InTime dept Manager Salary
101 2021-08-29 Dept1 Manager1 100000
101 2021-09-29 Dept1 Manager2 200000
102 2021-08-29 Dept2 Manager1 105000
102 2021-09-29 Dept2 Manager2 220000
103 2021-08-29 Dept3 Manager1 100000
103 2021-09-29 Dept3 Manager1 150000

[v-yangliu-msft]

Hi  @PreetMDX ,

Here are the steps you can follow：

1. Enter power query, Add Column – Index Column – From 1.

2. Create calculated column.

Flag =
var _max=MAXX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])),[InTine])
return
IF(
    [InTine]=_max,1,0)

Flag_Salary =
var _1=CALCULATE(SUM('Table'[Salary]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1))
var _if=IF(
    [Flag]=1,_1,99999999999)
return
IF(
    [Salary]>_if,"red","black")

Flag_Manager =
var _1=CALCULATE(MAX('Table'[Manager]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1))
var _if=IF(
    [Flag]=1,_1,99999999999)
return
IF(
    [Manager]<>_if && [Flag]=1,"red","black")

2. Select [Manager] column – Conditional formatting – Font color.

3. Enter the Font color interface, Based on field – [Flag_Manager].

4. Select [Salary] column – Conditional formatting – Font color.

5. Enter the Font color interface, Based on field – [Flag_Salary].

6. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PreetMDX]

Thank you @v-yangliu-msft 

This is working fine if there is a single entry in every month for an ID. if there are multiple entries like below:

ID InTime dept Manager Salary
106 2021-09-29 Dept1 Manager1 100000
106 2021-09-29 Dept2 Manager2 100000
106 2021-08-29 Dept1 Manager1 100000
106 2021-08-29 Dept2 Manager2 100000

Flags are set rue on each column whether two rows in each month are exactly the same as the previous month's rows.

@amitchandak, The same is happening if I use the earliest function as well, just as you have mentioned.

Sorry, I didn't consider this before. Could you help me find a way around this as well?

[amitchandak]

@PreetMDX , create a new flag column like

New column =
var _InTime = eomonth([InTime],-1)
var _dept = [dept]
var _Manager = [Manager]
var _Salary = [Salary]
var _1 = countx(filter(Table, eomonth([InTime],0) =_1 && [Id] = earlier([ID]) && ( [dept] <> _dept || [Manager] <> _Manager || [Salary] <> _Salary)),[ID])
return
if(isblank(_1), "same", "changed")

[PreetMDX]

Hi @amitchandak , Thank you for your quick response.

The aforementioned column is working fine as a change flag. However, I'm expecting the value color to be changed to distinguish  if the value is different from the previous month as follows:

ID InTime dept Manager Salary
101 2021-08-29 Dept1 Manager1 100000
101 2021-09-29 Dept1 Manager2 200000
102 2021-08-29 Dept2 Manager1 105000
102 2021-09-29 Dept2 Manager2 220000
103 2021-08-29 Dept3 Manager1 100000
103 2021-09-29 Dept3 Manager1 150000

According to your solution, I have to set a flag for each column and then use this in the if function to compare and color code.

[amitchandak]

@PreetMDX , you can use conditional formatting in table and matrix

Create a color measure and use that in conditional formatting using field value option

if(Max(Table[New column]) = "changed", "red", "Black")

PowerBI Abstract Thesis: How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[PreetMDX · Accepted solution]

Thank you, @amitchandak I'm able to conditional format this based on the flag.

The in Dax query you have created :

var _1 = countx(filter(Table, eomonth([InTime],0) =_1 && [Id] = earlier([ID]) && ( [dept] <> _dept || [Manager] <> _Manager || [Salary] <> _Salary)),[ID])

It is checking if any of the table fields are changed. 

In order to track data changes on separate fields. Do I need to create flags for all the fields?

For example, for ID 103 only Salary was changed not the Manager's name.