Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

Hello,
Below is the dataset :

EMP_IDEmployee NameStart Date keyEnd Date keyEmp Manager Name
999999ABC2016032120160930Mgr1
999999ABC2016100120161002Mgr2
999999ABC2016100320161106Mgr2
999999ABC2016110720170501Mgr2
999999ABC2017050220170522Mgr2
999999ABC2017052320170728Mgr3
999999ABC2017072920170730Mgr3
999999ABC2017073120171205Mgr3
999999ABC2017120620180129Mgr3
999999ABC2018013020180630Mgr3
999999ABC2018070120180902Mgr3
999999ABC2018090320190301Mgr4
999999ABC2019030220190915Mgr4
999999ABC2019091620191022Mgr4
999999ABC2019102320191023Mgr1
999999ABC2019102420191031Mgr1
999999ABC2019110120191102Mgr1
999999ABC2019110320211010Mgr1
999999ABC2021101120211231Mgr1


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_IDEmployee NameStart Date keyEnd Date keyEmp Manager Name
999999ABC2016032120160930Mgr1
999999ABC2016100120170522Mgr2
999999ABC2017052320180902Mgr3
999999ABC2018090320191022Mgr4
999999ABC2019102320211231Mgr1
4 REPLIES 4
Anonymous
Not applicable

please help

v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

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

vzhangti_0-1671609549472.png

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

vzhangti_1-1671609600728.png

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

 

Anonymous
Not applicable

@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.

Anonymous
Not applicable

Kindly help

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!