I am working with an employee table and was wondering if I could get some help.
My data table has rows with start and end date values. I filter these rows down using Filter(data table, [start date]<=[Measure.MaxMonth]&&[end date]>=[Measure.MaxMonth]
. [Measure.MaxMonth]
is a measure that sits on a disconnected date table and functions like a parameter.
Here is a formula that I have been testing but have not been getting the desired results:
Measure.DirectReports = CALCULATE(COUNTROWS(Data Table),filter(Data Table,Data Table[Mgr ID]=Data Table[Emp ID]&&Data Table[Start Date]<=[Meas.LastMonth]&&Data Table[End Date]>=[Meas.LastMonth]))
Measure.LastMonth = max(EOM[End of Month]) ->this value can equal any month end between July 2005 and July 2017. EOM is a date table with a row for each month end - 7/31/2005, 8/31/2005,....7/31/2017
This gives me a table structured liked this:
Emp ID,Emp Attr,Mgr ID,Start Date,End Date
1,B,4,10/1/2013,10/6/2013
1,B,4,10/7/2013,12/29/2013
1,B,4,12/30/2013,12/28/2014
1,B,8,12/29/2014,10/4/2015
1,B,8,10/5/2015,12/27/2015
1,B,12,12/28/2015,5/15/2016
1,B,12,5/16/2016,10/2/2016
1,B,12,10/3/2016,12/25/2016
2,B,4,12/1/2014,12/28/2014
2,B,4,12/29/2014,12/27/2015
2,B,4,12/28/2015,2/7/2016
2,B,4,2/8/2016,3/6/2016
2,B,8,3/7/2016,6/1/2016
3,B,6,7/1/2015,12/27/2015
3,B,8,12/28/2015,6/30/2016
3,B,6,7/1/2016,9/4/2016
3,B,6,9/5/2016,12/25/2016
3,B,6,12/26/2016,5/7/2017
3,B,4,5/8/2017,6/11/2017
3,B,4,6/12/2017,6/25/2017
3,B,4,6/26/2017,7/9/2017
3,B,19,7/10/2017,12/31/9999
4,A,,7/1/1996,4/2/2006
4,A,,4/3/2006,12/31/2007
4,A,,1/1/2008,5/22/2011
4,A,,5/23/2011,11/16/2014
4,A,,11/17/2014,6/11/2017
4,A,,6/12/2017,6/25/2017
4,A,,6/26/2017,12/31/9999
5,B,4,11/8/2010,1/2/2011
5,B,4,1/3/2011,5/22/2011
5,B,4,5/23/2011,1/1/2012
5,B,4,1/2/2012,5/31/2012
5,B,4,6/1/2012,7/1/2012
5,B,4,7/2/2012,9/7/2012
6,B,4,1/3/2011,5/22/2011
6,B,4,5/23/2011,9/5/2011
6,B,4,9/6/2011,1/1/2012
6,B,4,1/2/2012,12/30/2012
6,B,4,12/31/2012,12/29/2013
6,B,4,12/30/2013,5/18/2014
6,B,4,5/19/2014,11/16/2014
6,B,4,11/17/2014,12/28/2014
6,B,4,12/29/2014,3/22/2015
6,B,4,3/23/2015,12/27/2015
6,B,4,12/28/2015,3/6/2016
6,B,4,3/7/2016,8/21/2016
6,B,4,8/22/2016,10/30/2016
6,B,4,10/31/2016,12/25/2016
6,B,4,12/26/2016,1/8/2017
6,B,4,1/9/2017,5/7/2017
6,B,4,5/8/2017,6/11/2017
6,B,4,6/12/2017,6/25/2017
6,B,4,6/26/2017,12/31/9999
7,B,4,1/2/2012,12/30/2012
7,B,4,12/31/2012,12/29/2013
7,B,4,12/30/2013,5/18/2014
7,B,4,5/19/2014,11/16/2014
7,B,4,11/17/2014,12/28/2014
7,B,4,12/29/2014,3/8/2015
7,B,4,3/9/2015,1/18/2016
7,B,4,1/19/2016,2/19/2016
8,B,6,12/31/2012,11/3/2013
8,B,4,11/4/2013,12/29/2013
8,B,4,12/30/2013,1/26/2014
8,B,4,1/27/2014,5/18/2014
8,B,4,5/19/2014,11/16/2014
8,B,4,11/17/2014,12/28/2014
8,B,4,12/29/2014,3/22/2015
8,B,4,3/23/2015,12/27/2015
8,B,4,12/28/2015,7/1/2016
10,B,4,10/3/2011,12/18/2011
10,B,4,12/19/2011,12/30/2012
10,B,4,12/31/2012,2/23/2013
10,B,4,2/24/2013,11/20/2014
11,B,4,2/1/2011,2/27/2011
11,B,4,2/28/2011,5/1/2011
12,B,4,9/15/2012,12/31/2012
12,B,4,9/15/2012,12/31/2012
12,B,4,1/1/2013,12/31/2013
12,B,4,1/1/2013,4/30/2014
12,B,4,1/1/2014,4/30/2014
12,B,4,5/1/2014,11/16/2014
12,B,4,5/1/2014,12/28/2014
12,B,4,11/17/2014,11/30/2014
12,B,4,12/1/2014,12/28/2014
12,B,4,12/29/2014,12/27/2015
12,B,4,12/29/2014,12/30/2016
12,B,4,12/28/2015,12/30/2016
12,B,4,12/31/2016,12/31/2016
12,B,4,1/1/2017,6/11/2017
12,B,4,6/12/2017,6/25/2017
12,B,4,6/26/2017,7/9/2017
12,B,19,7/10/2017,12/31/9999
13,B,4,12/28/2015,9/4/2016
13,B,4,9/5/2016,12/25/2016
13,B,4,12/26/2016,6/11/2017
13,B,4,6/12/2017,6/25/2017
13,B,4,6/26/2017,12/31/9999
14,B,4,1/12/2015,12/27/2015
14,B,4,12/28/2015,12/25/2016
14,B,4,12/26/2016,6/11/2017
14,B,4,6/12/2017,6/25/2017
14,B,4,6/26/2017,12/31/9999
16,B,4,9/14/2015,10/19/2015
17,B,6,8/22/2016,12/25/2016
17,B,6,12/26/2016,5/7/2017
17,B,4,5/8/2017,6/11/2017
17,B,4,6/12/2017,6/25/2017
17,B,4,6/26/2017,7/9/2017
17,B,19,7/10/2017,12/31/9999
18,B,6,9/12/2016,12/25/2016
18,B,6,12/26/2016,5/7/2017
18,B,13,5/8/2017,6/11/2017
18,B,13,6/12/2017,6/25/2017
18,B,13,6/26/2017,7/9/2017
18,B,19,7/10/2017,12/31/9999
19,B,4,7/10/2017,12/31/9999
Empl ID is a unique employee number. Mgr ID references the Empl ID of the employee's manager at the desired point in time (Measure.LastMonth). Emp Attr is an attribute of the employee, such as level.
Does anyone have any ideas on how to create a measure that will count the occurrences of Empl
ID
in Mgr
ID
? Ideally, if I am creating a visual in Power BI and I filter based on Empl Attr ="A"
, can the resulting measure value give me the result = 3
-> the empl id "1" occurs 3 times in the mgr id column.
I need this to be a measure and not a calculated column so that I can trend the results over time (end of month on X axis in trend visual).
Thanks for the help and let me know if you have any questions!