A flag to track change in a column to get employee HC changes

[mikekim123]

Hi Team,

I have an employee HC table and i need to provide analysis for HC added and HC reduced per month.

To achieve this i am trying to create a flag that will keep track of changes by comparing this month and previous month but so far not getting the desired output. The table is having a format as shown below.

I want to produce columns "Change Flag" and HC Status

Is it possible to create the same using powerquery or dax?

I was able to get the first occurence of an entry using first occurence flag mentioned in below post : https://community.powerbi.com/t5/Power-Query/Flag-first-occurrence-item-in-Power-Query/td-p/2544782
but it doesnt work for repetative change for eg: Emp x got transferred from AEG to ASP then back to AEG, in this case the flag is returning 0 as it is not the first occurence.

[AlienSx]

Hello, @mikekim123 assuming that you have a table "Table1" of Employee Name, Period and Subsidiary in Excel (and btw sorting step is important!) 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"Period", type date}}),
    sorting = Table.Sort(types,{{"Employee Name", Order.Ascending}, {"Period", Order.Ascending}}),
    groups = 
        Table.Group(
            sorting, {"Employee Name", "Subsidiary"}, 
            {{"all", each Table.Sort(_, {"Period", Order.Ascending}), type table [Employee Name = text, Period=nullable date, Subsidiary=text]}}, 
            GroupKind.Local, 
            (s, c) => Number.From((s[Subsidiary] <> c[Subsidiary]) or (s[Employee Name] <> c[Employee Name]))
        ),
    fx_add_columns = (t as table) as table =>
        Table.FromColumns(
            Table.ToColumns(t) & {{1} , {"HC ADDED TO " & t[Subsidiary]{0}}},
            Table.ColumnNames(t) & {"Change Flag"} & {"HC Status"}
        ),
    almost_there = Table.Combine(Table.TransformColumns(groups, {"all", fx_add_columns})[all]),
    z = 
        Table.TransformColumns(
            almost_there, 
            {{"Change Flag", each Replacer.ReplaceValue(_, null, 0)},
            {"HC Status", each Replacer.ReplaceValue(_, null, "")}}
        )

in
    z