Forum Discussion

soji's avatar
soji
Frequent Visitor
5 years ago

Add new column using DAX 1

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      soji
      Frequent Visitor
      5 years ago

      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)
  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 years ago

    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] )
    )