Considering that you want to know the number of employees Joining and Leaving Month Wise... Use below in your script...
=================================
Employee:
Load
[Employee ID],
Date(Date#([Joining Date],'DD-MMM-YYYY')) as [Joining Date],
Date(Date#([Leaving Date],'DD-MMM-YYYY')) as [Leaving Date]
Inline
[
Employee ID, Joining Date, Leaving Date
1111, 12-Jan-2010, 13-Feb-2011
1112, 12-Jan-2010, 13-Feb-2011
1113, 12-Feb-2011, 12-Feb-2012
1114, 12-Feb-2011, 12-Feb-2012
1115, 14-Mar-2013, 14-Apr-2014
1116, 15-Apr-2013, 16-Apr-2014
];
Joining:
Mapping Load
[Employee ID],
[Joining Date]
Resident Employee;
Leaving:
Mapping Load
[Employee ID],
[Leaving Date]
Resident Employee;
DateBridge:
Load
[Employee ID],
ApplyMap('Joining',[Employee ID],Null()) as Date,
'Joining' as DataType
Resident Employee;
Load
[Employee ID],
ApplyMap('Leaving',[Employee ID],Null()) as Date,
'Leaving' as DataType
Resident Employee;
Join
Load
[Employee ID],
Date,
Month(Date) as Month
Resident DateBridge;
================================
Now create a Straight Table
Dimension
Month
Expression
For Employee Joining...
COUNT({<DataType = {'Joining'}>}DISTINCT [Employee ID])
For Employee Leaving...
COUNT({<DataType = {'Leaving'}>}DISTINCT [Employee ID])
