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