Need help to find managers according to Min start date key and Max end date key

[amty63]

Hello,
Below is the dataset :

EMP_ID	Employee Name	Start Date key	End Date key	Emp Manager Name
999999	ABC	20160321	20160930	Mgr1
999999	ABC	20161001	20161002	Mgr2
999999	ABC	20161003	20161106	Mgr2
999999	ABC	20161107	20170501	Mgr2
999999	ABC	20170502	20170522	Mgr2
999999	ABC	20170523	20170728	Mgr3
999999	ABC	20170729	20170730	Mgr3
999999	ABC	20170731	20171205	Mgr3
999999	ABC	20171206	20180129	Mgr3
999999	ABC	20180130	20180630	Mgr3
999999	ABC	20180701	20180902	Mgr3
999999	ABC	20180903	20190301	Mgr4
999999	ABC	20190302	20190915	Mgr4
999999	ABC	20190916	20191022	Mgr4
999999	ABC	20191023	20191023	Mgr1
999999	ABC	20191024	20191031	Mgr1
999999	ABC	20191101	20191102	Mgr1
999999	ABC	20191103	20211010	Mgr1
999999	ABC	20211011	20211231	Mgr1

I am trying to use below DAX but it is not working :

SUMMARIZE(DIM_EMP,DIM_EMP[EMP_ID],DIM_EMP[Employee Name],DIM_EMP[Emp Manager Name],"ST_Date",MIN(DIM_EMP[START DATE key]),"EN_Date",MAX(DIM_EMP[END DATE key]))

desired result is :

EMP_ID	Employee Name	Start Date key	End Date key	Emp Manager Name
999999	ABC	20160321	20160930	Mgr1
999999	ABC	20161001	20170522	Mgr2
999999	ABC	20170523	20180902	Mgr3
999999	ABC	20180903	20191022	Mgr4
999999	ABC	20191023	20211231	Mgr1

[amty63]

please help

[v-zhangti]

Hi, @amty63 

You need to add an index column to Power Query first. 

Column:

MIN = CALCULATE(MIN('Table'[Start Date key]),FILTER('Table',[Emp Manager Name]=EARLIER('Table'[Emp Manager Name])))

Date Min = 
var _1=CALCULATE(MAX('Table'[Emp Manager Name]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1))
var _2=IF([Emp Manager Name]=_1,[MIN],[Start Date key])
return
IF([Start Date key]=_2&&_1=BLANK(),_2,IF([Start Date key]=_2,[MIN],BLANK()))

MAX = CALCULATE(Max('Table'[End Date key]),FILTER('Table',[Emp Manager Name]=EARLIER('Table'[Emp Manager Name])))

Date Max = 
var _1=CALCULATE(MAX('Table'[Emp Manager Name]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1))
var _2=IF([Emp Manager Name]=_1,[MAX],[End Date key])
return
IF([End Date key]=_2,_2,BLANK())

Table:

Table 2 = 
Var _table=SUMMARIZE('Table','Table'[EMP_ID],'Table'[Employee Name],'Table'[Emp Manager Name],'Table'[Date Min],'Table'[Date Max])
Return
FILTER(_table,[Date Max]<>BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amty63]

@v-zhangti 
Thanks for the reply,
However, I noticed your solution's last row is reflecting 20211011, it should come 20191023 as per requirement, kindly help.

[amty63]

Kindly help