HR Dashboard query - source files and table

[Anonymous]

hi all,

I am fairly new to PowerBI, however doen basic handson and now getting to work.

Have to develop a develop a HR monthly dashboard with 2 input files - 1) Monthly exit employee list 2) Total closing Headcount employee list (more than 1000 active employees ) for the month. Asked to do this with minimal manual intervention.

My concern is how do i get the values of opening and closing HC of every month from the HC file .

For attrition  Table- i face no issues , i have my table getting refreshed by just placing the monthly exit file in the folder.

For Headcount (HC) - if i replace the Monthly HC file every month,  i would get an active HC file for my dashboard. however i also need to calculate YTD attrition rate and Annual attrition rate by depts for which i would need the the opening and closing HC of each month.

Ruminating over this , i have gt the following options but confused on how will it help my cause:

1. Maintain one excel in a folder which picks the closing and opening HC every month (thru formulas or scripts in excel), which can be imported/refreshed in Power BI. can calculate the attrition rate from there

2. Refresh one HC table in PowerBI evey month which will update the HC , then manipulate the data for removing the duplicates, add a flag for the releived employees by connecting to the attrition table- however not sure how this will give me the opening and closing HC of every month to be stored , will it be fool proof 

3.Have a HC table for every month , but is it useful to upload 1000 roes of data just to get the monthly opning and closing HC file.?

how will it impact my visualization - every month need to connect to new table to get the fresh visualization?

4. Update the closing and opening HC every month which is a manual task.

Request to the Power BI pros to please guide on this...

[amitchandak]

@Anonymous ,

Can you share sample data and sample output in table format?

refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[BA_Pete]

Hi @Anonymous ,

We'll need some examples of your two datasets to be able to fully understand your requirements. Please share either in table format in a reply by using this button:

Or share via OneDrive for business and provide link.

PLEASE REMOVE/REPLACE ANY SENSITIVE DATA BEFORE SHARING.

Pete

[Anonymous]

Dear all, 

Thanks for the quick reply. Pl find the sample table/datasets

Average HC April = (April Opening HC + April Closing HC) /2

Average HC May = (April Close HC+May Closing HC)/2

Average HC June = (May close HC+ June close HC)/2

YTD for that month%= Total exits from April till that month/Average Headcount of no .of months 

Rolling Annualised% =Total Voluntary Leaver/Average Headcount of no .of months *12/no of months

Pl find the tables/files i have

1. HC monthly excel contains Total closing HC for each month approx 1000 employees in total...

Each month file varies due to exits and joinees for that month.

HC month	Employee ID	Emp name	Location	DOJ	Designation	Age	Reporting Manager	Gender	Experience	Qualification
April 2020

2. Monthly exit file  

HC month	Employee ID (number)	Emp name	DOJ (dd-MM-yyyy)	Last working day (dd-MM-yyyy)	type of exit (voluntary/invol)	Reason for exit	Last Rating	Designation	Reporting Manager
April 2020

[BA_Pete]

Hi @Anonymous ,

Right, there's quite a bit to unpack here so I've not done your entire job for you, but hopefully I can provide a solid starter-for-ten.

First up, this requires a calendar table that is related to the headcount table that we'll create in a minute. The relationships should look like this:

Where calendar[date] > headcount[DOJ] is your active relationship, with two inactive relationships [date] > [LWD] (last working date), and [date] > [LWDnoNulls], which we'll also create in a minute.

In terms of the data, my recommended setup is that you create folders for your monthly reports and import the entire folder of each (headcounts and exits), and append them into large tables of each. I have assumed this setup in the following transformations so, where you see my Source steps, I am assuming that these are the appended tables.

TABLES:

In Power Query, go to New Source>Blank Query then in Advanced Editor paste each of my code segments over the default code, ensuring that you name the tables as commented in the first line of the code. You can then follow the steps I took to complete the transformations required.

// Call this table "exit"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBNC8IwDAbgvyI7C0s/tjZHURAExbt4GDpccVqp28F/b1LrJ+opa9eneZvVKhudgmsHEiRkwwxUqZHqwm0a31a8I3LQuUBr4m9exLPr4Se1UhVUJ84fqSjIoSBn+Dph/zkUiurMN+wIUcvUT8g3N68uD4VSC6rLqgtus+cdnYNlWDI03P071EZF2LdJxXaWFlK/qVl/rJ8pTRlZ3dWBj1Iyw46fTF9Q/nAIyDnHTXDnzp+aqCko4D0ryA/dvoRFww2mPmx9mg7NhCBnEQVneE613/Xn7kENFjq+85COpkHeJ3Vz6ys=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"HC month" = _t, #"Employee ID (number)" = _t, #"Emp name" = _t, DOJ = _t, LWD = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"HC month", type text}, {"Employee ID (number)", type text}, {"Emp name", type text}, {"DOJ", type date}, {"LWD", type date}})
in
    chgAllTypes

// Call this table "headcount"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZNa9wwEIb/StlzwPrwh3QsDYQuJARScgk5CNdg060dFOegfx+N4hKPYo3UywrvPp4dj94H+enpdGtsP34TTLDT1YkxLbRff5oZrnjF6opr1Zyer2JStqJFZJcma6h5N/XjcjH7ut0B3TKh/PpgrBn9KmTF2hTb6ab267356z95A1XDT19JJWTj1+tpgXYlq1jji3b6CJUdtHttrB3Cs3UVU74uO6zbtRI6GNbBQreCmINSEib2sA4vY6gsGmgj1bHmUPm8jKEJj/LUGDSrP8bwdoFL34NIo5r79cdop9d18X3Y8F8V03BHe3QH12EcQ9hooWA7Ei1rUfPQx2qn/g98U8PoUoXrTu66rrcHVEeobgB9nOZ+mNctQaGwPKQ72Oubxf5ettlxkYA5Y5J/diF0xWSiC86FlCjxqUFwrhlk+Jc1vYNrue3IR9y+v9jpUmQcJinjYpI2DtO0cZiljMMkaVyE0sZFMGlcxFIOYZR0KEbzDkV3ZMSIaEqMCM2IEdOkGAimxcAoHfdb4wqPF1cUdczljhZXGPM9SR8rriziCMwdKa4w3ojMHieuTIQ9mDlK3H9JgPjs2eDKBEBg9lxwpeHfobkzwRUG//w2D0XJRyAV/Qiks49gOvwIpdKPQDL+mKTzj1lSAIxmDMAwpQAiSQciMi8BvoF6mcJkxhcMU8JgMmNMBJPK7FnaGURSr1EYzNh1+RRRNAHdNpcryMyu6I6UksEznc08rFtq02iY1Y1XJrgAc/3HPr8D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"HC month" = _t, #"Employee ID" = _t, #"Emp name" = _t, DOJ = _t]),
    remHCmonth = Table.RemoveColumns(Source,{"HC month"}),
    remDupes = Table.Distinct(remHCmonth),
    mergeExit = Table.NestedJoin(remDupes, {"Employee ID"}, exit, {"Employee ID (number)"}, "exit", JoinKind.LeftOuter),
    expandExit = Table.ExpandTableColumn(mergeExit, "exit", {"LWD"}, {"LWD"}),
    addLWDnoNulls = Table.AddColumn(expandExit, "LWDnoNulls", each if [LWD] = null then Date.From(DateTime.LocalNow()) else [LWD]),
    chgAllTypes = Table.TransformColumnTypes(addLWDnoNulls,{{"LWDnoNulls", type date}, {"DOJ", type date}})
in
    chgAllTypes

 In summary, I've removed duplicates from the appended headcount table so we now just have a table with one row for each employee, then merged the exit table to this so we have a full record for each employee in a single table (when you expand the merge, you can select all of your exit table columns if you want).

Right, that's the data done. Apply this to the Data Model.

Once you have your new headcount table in the Data Model and related it to your calendar table above, you can start creating measures with a mix of time-intel and unrelated time views. Here's some useful measures that will work on my example data to get you going, just make sure that you use the date fields from the related calendar table as the axis for any visuals that feature these measures:

//Current headcount
_noofHeadsCurr = 
CALCULATE(
    DISTINCTCOUNT(headcount[Employee ID]),
    CROSSFILTER('calendar'[date], headcount[DOJ], None),
    headcount[LWD] = BLANK()
) +0

//Headcount at start of period in view
_nooHeadsStartPeriod = 
VAR mindate =
    MIN('calendar'[date])
VAR maxdate =
    MAX('calendar'[date])
RETURN
    CALCULATE(
        DISTINCTCOUNT(headcount[Employee ID]),
        CROSSFILTER('calendar'[date], headcount[DOJ], none),
        KEEPFILTERS(mindate >= headcount[DOJ]),
        KEEPFILTERS(mindate <= headcount[LWDnoNulls])
    )

//Headcount at end of period in view
_nooHeadsEndPeriod = 
VAR mindate =
    MIN('calendar'[date])
VAR maxdate =
    MAX('calendar'[date])
RETURN
    CALCULATE(
        DISTINCTCOUNT(headcount[Employee ID]),
        CROSSFILTER('calendar'[date], headcount[DOJ], None),
        KEEPFILTERS(maxdate >= headcount[DOJ]),
        KEEPFILTERS(maxdate <= headcount[LWDnoNulls])
    )

//Average headcount over period in view
_noofHeadsPeriodAvg = 
VAR mindate =
    MIN('calendar'[date])
VAR maxdate =
    MAX('calendar'[date])
VAR noofHeadsMin =
    CALCULATE(
        DISTINCTCOUNT(headcount[Employee ID]),
        CROSSFILTER('calendar'[date], headcount[DOJ], None),
        KEEPFILTERS(mindate >= headcount[DOJ]),
        KEEPFILTERS(mindate <= headcount[LWDnoNulls])
    )
VAR noofHeadsMax =
    CALCULATE(
        DISTINCTCOUNT(headcount[Employee ID]),
        CROSSFILTER('calendar'[date], headcount[DOJ], None),
        KEEPFILTERS(maxdate >= headcount[DOJ]),
        KEEPFILTERS(maxdate <= headcount[LWDnoNulls])
    )
VAR nooHeadsAvg =
    (noofHeadsMin + noofHeadsMax) / 2
RETURN
    IF(
        ISBLANK(nooHeadsAvg),
        BLANK(),
        nooHeadsAvg
    )

//Number of employees joined during period in view
_noofJoins = 
CALCULATE(
    DISTINCTCOUNT(headcount[Employee ID]),
    USERELATIONSHIP('calendar'[date], headcount[DOJ])
)

//Number of employees exited during period in view
_noofExits = 
    CALCULATE(
        DISTINCTCOUNT(headcount[Employee ID]),
        USERELATIONSHIP('calendar'[date], headcount[LWD]),
        NOT ISBLANK(headcount[LWD])
    )

//Exits as % of average headcount during period in view
_exit% = DIVIDE([_noofExits], [_noofHeadsPeriodAvg], 0)

//Headcount over time
_noofHeadsOverTime = 
VAR cDate = 
    MAX(calendar[date])
VAR heads =
    CALCULATE(
        DISTINCTCOUNT(headcount[Employee ID]),
        CROSSFILTER('calendar'[date], headcount[DOJ], None),
        KEEPFILTERS(cdate >= headcount[DOJ]),
        KEEPFILTERS(cDate <= headcount[LWDnoNulls])
    )
RETURN
    IF(
        ISBLANK(heads), BLANK(), heads)

//Number of exits YTD (assume 1st April year start)
_noofExitsYTD = 
CALCULATE(
    DISTINCTCOUNT(headcount[Employee ID]),
    NOT ISBLANK(headcount[LWD]),
    USERELATIONSHIP('calendar'[date], headcount[LWD]),
    DATESYTD('calendar'[date], "03/31")
)

These measures will alow you to start visualising employee position trends, rather than focusing on single months at a time, something like this:

Hopefully this gives you a pretty firm base to kick off from. If you take nothing else away from this mega-post, I certainly recommend you use the data structure that I have suggested This will make it much simpler to build your own measures from.

Pete