Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
We have the following situation: I want to have a linechart with the number of employees (y-axis) of the last 2 years (x-axis).
The only data I have available are the entry data and it the employee has left the company, the leaving date.
Example Data: Employees' Lastname; Entry date; Leaving date
Best regards,
Peter
Solved! Go to Solution.
Give this a try:
1) Unpivot your data in the Query Editor (highlight the "Entry Date" and "Leaving Date" columns, click on the Transform tab, select "Unpivot Columns", then rename the columns
2) Create a date table using CALENDAR, for example"
Date = CALENDAR("1/1/2014", "12/31/2019")
3) Create a relationship between Date[Date] and EmpTab[Dt]
4) Create the following measure ActiveEmployees
ActiveEmployees =
CALCULATE (COUNTA ( EmpData[Name]), FILTER ( ALL ( EmpData ), EmpData[Type] = "EntryDate" && EmpData[Dt] <= MAX ( EmpData[Dt] ) ) ) -
CALCULATE ( COUNTA ( EmpData[Name]), FILTER ( ALL ( EmpData ), EmpData[Type] = "LeavingDate" && EmpData[Dt] <= MAX ( EmpData[Dt] ) ) )
5) Create a line graph with Date[Date] on X-axis and [ActiveEmployees] on Y-axis. Date[Date] will likely automatically create a hierarchy of Year-Qtr-Month-Day - if you want to keep it, drill in the visual to the desired level. If you just want the date plotted, click the dropdown for Date and select "Date" instead of "Date Hierarchy"
Hope this helps
David
Hi Peter
I made a small sample which hopefully guides you in the right direction.
First I created a calendar table. Easiest way to do so is using the "New Table" option and feed it with this formula (start and end date depending on your needs):
Calendar = CALENDAR(DATE(2016;1;1);DATE(2017;12;31))
In the calendar table I added the Column "NumberOfEmp" using this expression:
NumberOfEmp = COUNTAX( FILTER( Employees; Employees[DateEntry]<=EARLIER(Calendar[Date].[Date])); Employees[DateEntry]) - COUNTAX( FILTER( Employees; Employees[DateLeaving]<=EARLIER('Calendar'[Date].[Date])); Employees[DateLeaving]) +0
Explanation:
COUNTAX() iterates through all rows in the employees table and counts the occurances of DateEntry Values. FILTER() and EARLIER() ensure that only entries with a day earlier or equal to the current row in the calendar table are count.
The first part of the formula counts all Entries, the second part the leavings.
With this table in place you can create your visuals. I made a table and made sure the "NumberOfEmp" are not summarized.
If you look carefully you see that on the monthly level there are 2 entries for December 2016 because there's a Leaving on Dec 31, resulting in multiple values throughout the month.
When you create a line graph you must choose a summarizaion, as "Don't summarize" is not available. So you can choose either MIN or MAX resulting in slightly different values (eg. 3 or 4 for Dec 16).
I hope this helps
JJ
Give this a try:
1) Unpivot your data in the Query Editor (highlight the "Entry Date" and "Leaving Date" columns, click on the Transform tab, select "Unpivot Columns", then rename the columns
2) Create a date table using CALENDAR, for example"
Date = CALENDAR("1/1/2014", "12/31/2019")
3) Create a relationship between Date[Date] and EmpTab[Dt]
4) Create the following measure ActiveEmployees
ActiveEmployees =
CALCULATE (COUNTA ( EmpData[Name]), FILTER ( ALL ( EmpData ), EmpData[Type] = "EntryDate" && EmpData[Dt] <= MAX ( EmpData[Dt] ) ) ) -
CALCULATE ( COUNTA ( EmpData[Name]), FILTER ( ALL ( EmpData ), EmpData[Type] = "LeavingDate" && EmpData[Dt] <= MAX ( EmpData[Dt] ) ) )
5) Create a line graph with Date[Date] on X-axis and [ActiveEmployees] on Y-axis. Date[Date] will likely automatically create a hierarchy of Year-Qtr-Month-Day - if you want to keep it, drill in the visual to the desired level. If you just want the date plotted, click the dropdown for Date and select "Date" instead of "Date Hierarchy"
Hope this helps
David
What @dedelman_clng said is the right approach, but I would add two things:
1. Create the date table in Power Query, not DAX, if you want to create a more robust date table (i.e. quarters, months, week number, etc.).
2. Generate a dynamic start and end date for your date table using the min value of your hire table and today's date (or some other date for the end date).
The reason for #1 is that you will get better data compression and performance using a table that is created in Power Query rather than DAX (especially if it is more complex than just days). The reason for #2 is that this way, as your employee hiring table grows, it will update automatically.
@dkay84_PowerBI - can you elaborate on #2 and how you would do that in PowerQuery (also, I'm assuming PowerQuery is the "Query Editor" function in PBI? I've done some minor custom coding in the "Advanced Editor" but never referencing another table in the model.
Sure! Please see the following:
Hi,
We have the following situation: I want to have a linechart with the total number of employees (y-axis) of the last 2 years (x-axis) on a monthly or daily basis.
The only data I have available are the entry date of the employee and, if the employee has left the company, the leaving date.
Example of the data (3 colums: Employee; Entrydate; Leaving date):
Best regards,
Peter
Hi Peter
I made a small sample which hopefully guides you in the right direction.
First I created a calendar table. Easiest way to do so is using the "New Table" option and feed it with this formula (start and end date depending on your needs):
Calendar = CALENDAR(DATE(2016;1;1);DATE(2017;12;31))
In the calendar table I added the Column "NumberOfEmp" using this expression:
NumberOfEmp = COUNTAX( FILTER( Employees; Employees[DateEntry]<=EARLIER(Calendar[Date].[Date])); Employees[DateEntry]) - COUNTAX( FILTER( Employees; Employees[DateLeaving]<=EARLIER('Calendar'[Date].[Date])); Employees[DateLeaving]) +0
Explanation:
COUNTAX() iterates through all rows in the employees table and counts the occurances of DateEntry Values. FILTER() and EARLIER() ensure that only entries with a day earlier or equal to the current row in the calendar table are count.
The first part of the formula counts all Entries, the second part the leavings.
With this table in place you can create your visuals. I made a table and made sure the "NumberOfEmp" are not summarized.
If you look carefully you see that on the monthly level there are 2 entries for December 2016 because there's a Leaving on Dec 31, resulting in multiple values throughout the month.
When you create a line graph you must choose a summarizaion, as "Don't summarize" is not available. So you can choose either MIN or MAX resulting in slightly different values (eg. 3 or 4 for Dec 16).
I hope this helps
JJ
Hi.
The solution with the Calendar-Table and with COUNTAX / FILTER worked perfectly and generated a nice Chart.
Thx a lot!!!!