Dynamic Employee Headcount Aggregation by Fiscal Year

[ryanzimmerlee]

Hello everyone,

New user of DAX here so go easy on me.  🙂  

In Power BI, I'm trying to create a measure that dynamically aggregates or counts active employees based the fiscal year selected.  Here's a glimpse of the data I'm working with:  

Table1

ID	FTE	Status	Status Change Date
1	0.5	A	20080628
1	0.5	I	20100226
1	1	A	19830129
2	1	A	20080628
2	1	I	20140527
2	1	A	20000110
3	1	A	20080628
3	1	A	20060227
4	1	A	20080628
4	1	A	20040504
5	1	A	19990920
5	1	A	20080906

Using a date table (seen below), I'm trying to create a formula that for every month will count employees that are active during that month (as well as FTE = 1).  So, for example, Employee 1 was active and full-time each month and fiscal year all the way up until 2008, where he dropped to part-time but was still active.  Eventually Employee 1 left in February 2010, where then the status is set to "I".  

I have solved for active employees as of the current date, but have not been able to determine how to do so historically.  To top it off, I'd like to take an average of each month's headcount across each fiscal year.  

A fiscal year is defined by 7/1/XX - 6/30/XX.  

Is this possible using DAX calculated columns and measures or am I better off ingesting a pre-aggregated table using SQL?  

Date table columns that are relevant:

DateTable

Date ID	Date Full ID	Fiscal Year	Fiscal Month
20080101	1/1/2008	FY2007	7
20080102	1/2/2008	FY2007	7
20080103	1/3/2008	FY2007	7
20080104	1/4/2008	FY2007	7
20080105	1/5/2008	FY2007	7
20080106	1/6/2008	FY2007	7
20080107	1/7/2008	FY2007	7
20080108	1/8/2008	FY2007	7
20080109	1/9/2008	FY2007	7
20080110	1/10/2008	FY2007	7
20080111	1/11/2008	FY2007	7
20080112	1/12/2008	FY2007	7

Thanks very much in advance.  Please let me know if I was unclear or can provide additional information.

Cheers,

Ryan

[v-frfei-msft]

Hi @ryanzimmerlee ,

To create a measure as below.

Measure = 
VAR discount =
    CALCULATE (
        DISTINCTCOUNT ( Table1[ID] ),
        FILTER (
            Table1,
            Table1[FTE] = 1
                && Table1[Status] = "A"
                && Table1[Status Change Date] >= MIN ( 'date'[Date ID] )
                && 'Table1'[Status Change Date] <= MAX ( 'date'[Date ID] )
        )
    )
VAR monttc =
    DISTINCTCOUNT ( 'date'[Fiscal Month] )
RETURN
    DIVIDE ( discount, monttc )

[ryanzimmerlee]

Hi Frank (@v-frfei-msft ),

Thanks for taking the time to try and find a solution for me.  Your logic makes sense but I don't think it's fully working for this application.  

If I want to find active employees for a given Fiscal Year, it also needs to include where employees latest record (based on their [Status Change Date]) is [Status] = "A".  

If I'm understanding correctly, the logic you've provided only captures employee records between the beginning and end fiscal year dates (07/01/XX and 6/30/XX) where there is a [Status Change Date] in that year (and of course being full-time and active).  The result is a much smaller subset than anticipated.  

For example, if we were to do a COUNT instead of DISTINCTCOUNT for FY2008, you would be returned with employees 2, 3, and 4 based on the above logic since they are active, full-time and have a status change date between 07/01/2007 and 06/30/2008.  The DISTINCTCOUNT is only grabbing 1 of these employees, however, since each employee shares the same 20080628 status change date, hence returning = 1 employee / 12 months = 0.083.  

This is great foundation code and I will continue to extrapolate off this.  If you think of any way to tackle the aggregation of active employees a different way, let me know...

Here is a pretty ugly, yet logical example of the example count for the sample data:

[ryanzimmerlee · Accepted solution]

Circling back to say that I've solved my own problem.  Appreciate the help, Frank.

Month :=

VAR lessThanDate = 
FILTER(
    'Table1',
    [Status Change Date] <= 20170731  // Isolated one month to test headcount logic
       )

VAR rankLatestDate = 
ADDCOLUMNS(lessThanDate,"Rank", 
RANKX( 
    FILTER(
        lessThanDate,
        [ID] = EARLIER([ID])),
        [Status Change Date],
        ,
        DESC,
        DENSE
        )
    )

VAR filterCount = 
FILTER(
    rankLatestDate,
        [Rank] = 1 
            && [Status] = "A" 
            && [FTE] = 1)

VAR cntRows = COUNTROWS(filterCount)

RETURN 
    cntRows

Now to make it dynamic using the date table.