Upcoming 3 employee 3 anniversary day dax

[Anonymous]

Hi Power BI team,

Below is my data , I need Upcoming 3 employee Anniversary list required.

Depart Name	Employee Name	JoingDate
AA	A	25-09-2018
AA	B	25-09-2019
AA	C	01-09-2019
AA	D	01-09-2017
AA	E	01-09-2015
AA	F	11-12-2014
AA	G	01-01-2019

Below is my expected output.

please help me how write DAX

Depart Name	Employee Name	JoingDate	upcoming 3  anniversary
AA	A	25-09-2018	2 YEAR
AA	B	25-09-2019	1 YEAR
AA	F	11-12-2014	5 YEAR 9 MONTH

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Check the measure below.

Column = 
var diffmonth = DATEDIFF('Table'[JoingDate],TODAY(),MONTH)
var year = ROUNDDOWN(diffmonth/12,0)
var month = MOD(diffmonth,12)-1
return
IF(month=0,year&" year",year&" year "&month&" month")

Not sure why there's only 3 rows in your expected out put, could you show the logic?

Best Regards,

Jay

[amitchandak]

@Anonymous , Try a measure like

calculate(datediff(max(Table[JoingDate]),today(),month), filter(Table, format(Table[JoingDate],"mmddd") >= format(today(),"mmddd")))

This will give in month, You can convert to year and month