Split Table and Re-Join

[sc4ry]

Dear all,

after hours of trying I finally resigned as I did not do any step forward =/

I am even not sure if I really need to Split and Re-Join the table, but I have a working model in KNIME which exactly does this and is working. Nevertheless I need to rebuild this in Power BI.

Maybe let me first try to describe the situation:

I have one list, which contains information on indivudual hirings and leavings. In case of leavings of course, these appear at least 2 times in the list, in case of Re-Entries over years even more. I need to evaluate these employees who left already again within first 6 months after hiring, but the information is "hidden" in multiple rows.

What I did in KNIME: I splitted hirings and leavings and used left outer join to just add the month of exit to the hiring list, calculated the difference of both dates and that´s it.

As I might need to split the list with CREATETABLE I am not able to use the MERGE QUERIES in Power Query Editor (or?).

I already created 2 seperate tables but could not join them together using GENERATE/ALL or NATURALLEFTOUTERJOIN although I created a relationship of the Person ID. 

[sc4ry]

Hey,

of course somehow your measures are working but at the end it does not help to solve my problem. Of course this is very much related to my bad description. But due to your formulas, maybe I do not even need a new table but just can solve this with some new columns in the existing one.

Please see below a very shortend part of my table. It has much more columns and thousands of rows as this table mixes per month the active employees (column FTE/HC) as well as hirings and leavings (column FTE Change/HC Change). Due to FTE-Splits, we even can have multiple columns for active employees as well as hirings and leavings. Good thing is, that we need to solve it for HC anyway, so we can focus on HC Change (+1 = hirings, -1 = leavings).

Due to seasonal work, employees might even appear multiple times as hiring and leaving, like in the example.

The columns I need formulas for, are the last 3 ones, whereas "Hiring Period" might not be needed or could be easily solved with something like: IF(Details[HC Change]=1;Details[Period];BLANK())

Drop-Off Diff is also not really a big challange, but how to calculate the Leaving Period? If possible, I would always like to check for the Leaving Period right after the Hiring Period.

I hope I could make it as clear as possible and the table is usefull too. 

Period	Person ID	FTE	HC	FTE Change	HC Change	Leaving Period	Hiring Period	Drop-Off Diff
01.07.2018	XYZ	1	1	1,00	1	01.09.2018	01.07.2018	2
01.08.2018	XYZ	1	1	0,00	0
01.09.2018	XYZ	0	0	-1,00	-1
01.07.2019	XYZ	1	1	1,00	1	01.09.2019	01.07.2019	2
01.08.2019	XYZ	1	1	0,00	0
01.09.2019	XYZ	0	0	-1,00	-1

Sorry, that it was not that clear from the beginning, but due to the measures I came to the conclusion, that it would be even much better to add this to the existing table instead of creating a new one. 

[amitchandak]

@sc4ry It is not making it clear. Also was there a need to split table. Check this HR solution in my blog if that can help.

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

[sc4ry]

Hey,

the difference between our tables is exactly my issue. My data looks like the following:

Employee Period HC Change

A 12/19 +1

B 01/20 +1

C 01/20 +1

A 02/20 -1

C 03/20 -1

Out of this I want to create a table like you have.

Employee Entry Period Exit Period

A 12/19 02/20

C 01/20 03/20

[v-kelly-msft]

Hi @sc4ry ,

1, Go to edit queries and split your table into 3 columns:

2.Then you need 2 measures as below:

Hiring = CALCULATE(MIN('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]),'Table'[Column3]=1)

leaving = 
CALCULATE(MAX('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]),FILTER('Table',DATEDIFF(MAX('Table'[Column2]),'Table'[Hiring],MONTH)<6 &&'Table'[Column3]=-1))

3.Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[sc4ry]

Thanks a lot. I already started but not yet trough with the validation, but it looks promosing. I will come back again, at least to check the solution as far I am really sure 😉

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490