Reshaping data with columns 'date from' and 'date to'

[rammishra]

Hello everyone!

I am new to Power BI and stuck with the structure of my dataset. Following is the summary of the data and challenges.

My data contains details of individuals in leadership positions at various locations. The data has four columns:

1. Name of the location (Text field)

2. Name of the person (Text field)

3. Sex of the person (Man/ Woman) 

4. Date when a person started working at the position (Date format)

5. Date when a person's tenure ended (Date format)

I want to create a line chart showing the percent of women leaders by year. Screenshot of a sample data and required chart are shown below. Is there any way, I can reshape the data in a way that enables me to create the following chart? Is it possible to generate this chart without reshaping the data?

Your suggestions will be highly appreciated.

Thanks 

Ram

[rammishra · Accepted solution]

Thanks a lot, for your response.

The problem is that I want years 2001 to 2020 to appear on x-axis whereas the years are grouped in two dates column (tenure start date and tenure end date). 

Note: The file is not available for download.

Thanks again for your prompt response. 

Regards

Ram

[amitchandak]

@rammishra , you have to create a similar solution what is shown in this blog. check for current employee and create another on with women filter

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[lkalawski]

@rammishra

I prepared for you the file with the solution. 

Please find the file: https://file.io/PkWZwmQtOcxR

You don't need to change anything in the database structure. All you need to do is add a measure that calculates the number of people in a given year, then add the second filter = "Woman" and calculate the percentage. 

_______________
If I helped, please accept the solution and give kudos! 😀 

[rammishra · Accepted solution]

Thanks a lot, for your response.

The problem is that I want years 2001 to 2020 to appear on x-axis whereas the years are grouped in two dates column (tenure start date and tenure end date). 

Note: The file is not available for download.

Thanks again for your prompt response. 

Regards

Ram

[lkalawski · Accepted solution]

@rammishra

You need to create Date table, e.g. by using CALENDAR(MIN(start_date), MAX(end_date)). I did it in the file, so please try again by using this link: https://gofile.io/d/3A5Bm7

_______________
If I helped, please accept the solution and give kudos! 😀 

[rammishra]

Thanks a lot. This is exactly what I was looking for. Kudos!!