Add column to show 'new hires' - HR data

[SophRow]

Hi,

I have a large dataset from HR which contains headcount reports that are run each month.

I want to create a new column called 'Hired in the Month' that contains the value 'Hired' if the hire date falls within the report month date. So the output would look like the below.

Any help greatly appreciated 🙂

Soph

[PhilipTreacy · Accepted solution]

Hi @SophRow 

Download example PBIX file

You haven't said if you want the column created in Power Query or in DAX so here's both 

PQ

= if Date.Month([Report Month]) = Date.Month([Hire Date]) then "Hired" else null

DAX Column

Hired DAX = IF(MONTH([Report Month]) = MONTH([Hire Date]), "Hired")

If you just want to use a measure and not create a column

Hired Measure = IF(MONTH(SELECTEDVALUE('DataTable'[Report Month])) = MONTH(SELECTEDVALUE('DataTable'[Hire Date])), "Hired")

Regards

Phil

[SophRow · Accepted solution]

Done! 

= Table.AddColumn(#"Added Conditional Column", "Custom", each if Date.Month([Report Month]) = Date.Month([Hire Date]) and Date.Year([Report Month]) = Date.Year([Hire Date]) then "Hired" else null)

[PhilipTreacy · Accepted solution]

Hi @SophRow 

Download example PBIX file

You haven't said if you want the column created in Power Query or in DAX so here's both 

PQ

= if Date.Month([Report Month]) = Date.Month([Hire Date]) then "Hired" else null

DAX Column

Hired DAX = IF(MONTH([Report Month]) = MONTH([Hire Date]), "Hired")

If you just want to use a measure and not create a column

Hired Measure = IF(MONTH(SELECTEDVALUE('DataTable'[Report Month])) = MONTH(SELECTEDVALUE('DataTable'[Hire Date])), "Hired")

Regards

Phil

[SophRow]

Hi Philip - sorry i just checked and it's doesn't work as it only takes month into account and not year  - can you possibly help again?