Add new column using DAX 1

[soji]

Hi

I have a table in power BI as mentioned below in yellow highlighted columns and i would like to add a new column using DAX as shown in blue color . Can you please help.

Thanks

Soji

[wdx223_Daniel]

@soji try this code

=
VAR _ChangeLineAfter =
    TOPN (
        1,
        FILTER (
            Table,
            Table[Employee Name] = EARLIER ( Table[Employee Name] )
                && Table[Month & Year] > EARLIER ( Table[Month & Year] )
                && Table[Department Change] = "YES"
        ),
        Table[Month & Year], ASC
    )
VAR _ChangeLineBefore =
    TOPN (
        1,
        FILTER (
            Table,
            Table[Employee Name] = EARLIER ( Table[Employee Name] )
                && Table[Month & Year] <= EARLIER ( Table[Month & Year] )
                && Table[Department Change] = "YES"
        ),
        Table[Month & Year]
    )
RETURN
    COALESCE (
        MAXX ( _ChangeLineBefore, Table[New Department] ),
        MAXX ( _ChangeLineAfter, Table[Old Department] )
    )

[amitchandak]

@soji , Create a new date column

date = "01-" & [Month & Year]

Try a new column like

new column =
var _1 = maxx(filter(Table, Employee =earlier([employee]) && [Date] >earlier(Employee])),firstnonblank(Table[Date],[Old Department]))
var _2 = maxx(filter(Table, Employee =earlier([employee]) && [Date] <=earlier(Employee])),lastnonblank(Table[Date],[New Department]))
return
if(isblank(_2),_1,_2)

[soji]

Hi Amit,

I tried as you suggested but all lines are filled with last "New Department" Date.

Department = //IF('Employee monthwise'[Old Dept]=BLANK(),CALCULATE(FIRSTNONBLANK('Employee monthwise'[Old Dept],TRUE())))

Var OldDep = MAXX(FILTER('Employee monthwise','Employee monthwise'[Emp Id] = EARLIER('Employee monthwise'[Emp Id]) && 'Employee monthwise'[Date]>EARLIER('Employee monthwise'[Date])),FIRSTNONBLANK('Employee monthwise'[Date],'Employee monthwise'[Old Dept]))

Var NewDep = MAXX(FILTER('Employee monthwise','Employee monthwise'[Emp Id] = EARLIER('Employee monthwise'[Emp Id]) && 'Employee monthwise'[Date]<=EARLIER('Employee monthwise'[Date])),FIRSTNONBLANK('Employee monthwise'[Date],'Employee monthwise'[New Dept]))

Return

IF(ISBLANK(NewDep),OldDep,NewDep)