Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @SophRow
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
Proud to be a Super User!
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)
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)
Hi @SophRow
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
Proud to be a Super User!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |