Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Peter_Scherrer
Regular 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
dedelman_clng
Community Champion
Community Champion

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

View solution in original post

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.PNGDon't summarize!Don'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

 

 

 

View solution in original post

7 REPLIES 7
dedelman_clng
Community Champion
Community Champion

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

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.

Peter_Scherrer
Regular Visitor

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

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.PNGDon't summarize!Don'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

 

 

 

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

Thx a lot!!!!

Chart.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.