Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 :
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 |
please help
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
@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.
Kindly help
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
121 | |
109 | |
81 | |
67 | |
67 |