Monthly join and leave

[Anonymous]

Hi, 

I would like to calculate the total monthly join and leave, The data is as below : 

Name	Date Join	Date Leave	User Type
A1	16/06/2001	08/07/2019	Internal
A2	03/01/2012	29/04/2019	Internal
A3	23/05/2013	17/10/2019	External
A4	14/03/2016	06/11/2019	External
A5	14/03/2016 00:00:00		External
A6	13/03/2017 00:00:00		External
A7	13/03/2017 00:00:00		External
A8	20/03/2017 00:00:00	06/11/2019 07:24:33	Internal
A9	19/03/2017 00:00:00	01/01/2020 00:00:00	External
A10	14/03/2016 00:00:00		External
A11	14/03/2016	06/11/2019	Internal
A12	13/03/2017		Internal
A13	13/03/2017	11/01/2020	External
A14		11/01/2020	Internal
A15	19/03/2017	01/12/2019	Internal

The employees could have the planned join/leave date. And they can have the date leave but not the date join 😞

The join and leave must be separated by the User Type (Internal / External)

Could anyone here help please ?

Many thanks in advance.

[amitchandak]

You have joined both join and leave date with a date dimension. In case you do not have one, please refer and create

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Once you do it, one join will be active and another one would be inactive. You need to use userelation in your calculation to choose the join.

Please refer the similar article

https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi,

Thank you for your reply, I had a look but sorry I am new to PBI and it's not very clear for my case. 

I created 2 measures : 

Join = IF (

CALCULATE (

COUNT ( Ressource[NewDateDebut] );

USERELATIONSHIP ( Calendrier[Date]; Ressource[NewDateDebut] )

)

= BLANK ();

0;

CALCULATE (

COUNT (Ressource[NewDateDebut] );

USERELATIONSHIP (Calendrier[Date]; Ressource[NewDateDebut] )

)

)

Leave = IF (

COUNT ( Ressource[NewDateFin] ) = BLANK ();

0;

COUNT ( Ressource[NewDateFin] )

)

The problem is that the X Asix didn't filter when I change the date slicer. 

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please make sure there is active relationship between x-axis and slicer.

You can create active relationship between calendrier[date] and DateJoin and inactive relationship between calendrier[date] and DateLeave then use calendrier[date] as slicer and x-axis.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

 If possible please share a sample pbix file after removing sensitive information.
Thanks