cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peter_Scherrer Frequent Visitor
Frequent Visitor

Data in the course of time

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

  • Name 1 ; 1/1/16; none
  • Name 2 ; 3/1/16; 7/1/16
  • Name 3 ; 4/1/16; none
  • Name 4 ; 6/1/16; none
  • Name 5; 8/1/16; 8/31/16
  • Name 6; 10/1/16

Best regards,

Peter

2 ACCEPTED SOLUTIONS

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: Data in the course of time

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

 

before.PNGafter.PNG

 

 

 

 

 

 

 

 

 

 

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"

 

graph.PNGdate.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps

David

DoubleJ Member
Member

Re: Data in the course of time

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.

COT_01.PNGCOT_02.PNGDon't summarize!

 

 

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). 

COT_03.PNG

 

 I hope this helps

JJ

 

 

 

7 REPLIES 7
Peter_Scherrer Frequent Visitor
Frequent Visitor

Data in the course of time

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):

  • Employee 1; 1/1/2016; none
  • Employee 2; 2/1/2016; 4/1/2016
  • Employee 3; 6/1/2016; none
  • Employee 4; 7/1/2016; none
  • Employee 5; 8/1/2016; 12/31/2016

 

Best regards,

Peter

dedelman_clng New Contributor
New Contributor

Re: Data in the course of time

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

 

before.PNGafter.PNG

 

 

 

 

 

 

 

 

 

 

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"

 

graph.PNGdate.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hope this helps

David

dkay84_PowerBI New Contributor
New Contributor

Re: Data in the course of time

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.

dedelman_clng New Contributor
New Contributor

Re: Data in the course of time

@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.

dkay84_PowerBI New Contributor
New Contributor

Re: Data in the course of time

DoubleJ Member
Member

Re: Data in the course of time

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.

COT_01.PNGCOT_02.PNGDon't summarize!

 

 

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). 

COT_03.PNG

 

 I hope this helps

JJ

 

 

 

Peter_Scherrer Frequent Visitor
Frequent Visitor

Re: Data in the course of time

Hi.
The solution with the Calendar-Table and with COUNTAX / FILTER worked perfectly and generated a nice Chart.

Thx a lot!!!!

Chart.JPG