update column fields based on max in other column

[Alina_Bd]

Hi,

Could you please help me to write a dax for below case:

I need to update all Manger Name field based on new name which appears, like in below excample, all Davis should be replaced with Smith as this is the newest one:

Employee ID  I Manger Name I Report Date

03                   I Smith               I 201910

03                   I Davis                I 201909

03                   I Davis                I 201908

03                   I Davis                I 201907

Thanks for help!

[Anonymous]

Hi,

You will need to have a Date Column. The Following column can't be used with this formula. For instance, if the Report Date is 201904 have another Date column with value 01-04-2019. Use that Date Column in the below DAX Code.

New_Column:=  VAR Max_Date= MAX(Table[Report_Date])
RETURN IF(Table[Report_Date]<= Max_Date, "Smith", "")

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue 🙂

[Alina_Bd]

Thanks! looks nice and I have other column i date format;
Is there a possibility to have it working for whole table/all Empl - as each month there are hundreds of Managers change

[Anonymous]

Hi,

So, for each month, the Manager who is in the MAX Date should be for all the Dates in that Month? If that is the case then I assume that you have the Month Column in the Table as a separate column. Then, you can use the below DAX.

Lookup_Table:= ADDCOLUMNS(SUMMARIZECOLUMNS(Table[Month], "Maximum Date", MAX(Table[Date])),"Manager_Name",LOOKUPVALUE(Table[Manager_Name],[Maximum Date],Table[Date]))


Orginal_Table[New_Column]:=LOOKUPVALUE(Lookup_Table[Manager_Name],Orginal_Table[Month],Lookup_Table[Month])

Try Creating a new Table with the above formula as Lookup_Table. Once that is done, Assuming that you have the Month Column in your current Table, you can use LOOKUPVALUE to bring in the Values from the Second Table into First Table. Let me know if this is working or throwing a Circular Dependency error.

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue 🙂

[Alina_Bd]

Thnaks!

I need an advice to implmet your solution:

Table[Month] - this should be number? or in which format

Table[Date] - in date format - like 10/3/2019?

[Maximum Date]  - not sure which field it is?

[Anonymous · Accepted solution]

Hi,

1. This should be in Whole Number format.

2. Date Format Correct

3. This Field comes in from the Temp Table