Forum Discussion
Adding calculated columns based on lookup table
You may add measures as shown below.
Beginning Head Count =
VAR startDate =
MAX ( Period[start date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Assignment[Assignment ID] ),
FILTER (
Assignment,
Assignment[Start Date] < startDate
&& (
ISBLANK ( Assignment[End Date] )
|| Assignment[End Date] >= startDate
)
)
)
New hires =
VAR startDate =
MAX ( Period[start date] )
VAR endDate =
MAX ( Period[end date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Assignment[Assignment ID] ),
FILTER (
Assignment,
Assignment[Start Date] >= startDate
&& Assignment[Start Date] <= endDate
)
)
Hello v-chuncz-msft
Thanks for your reply,
I am trying the measure as you suggested on test report but the results are not as intended.
As shown in first picture, if I am not selecting any year I get my beginning headcount which doesn’t match with the actual data,
And If I select any year from dropdown, I only can see my New Hires count for that number.
For this report, I want the cumulative beginning headcount and only new hires number for each period as per selected year, and I don’t know how to get this in one table. Can you please help me with this
Note: I have established connection between 'Period' table and 'Assignment' table by creating a 'Date ‘Column in my period table using power Query as (1 to M between Period[Date] to Assignment[StartDate])
Thank you.
Hello v-chuncz-msft here is the simplified data model.
Sample DataModel for headcount report
AssignmentTable
Id
AssignmentStartDate
AssignmentEnddate
1
12/17/2014
02/15/2015
2
01/05/2015
null
3
01/12/2015
04/30/2015
4
01/21/2015
04/23/2015
5
02/16/2015
null
6
02/23/2015
07/31/2015
Period Table
Year
Period
PeriodStartDate
PeriodEndDate
Date
2014
13
11/24/2014
12/21/2014
12/17/2014
2015
1
12/22/2014
01/18/2015
01/05/2015
2015
1
12/22/2015
01/18/2015
01/12/2015
2015
2
01/19/2015
02/15/2015
01/21/2015
2015
3
02/16/2015
03/15/2015
02/16/2015
2015
3
02/16/2015
03/15/2015
02/23/2015
HeadCount Report for 2015:
Period
Beginning HeadCount
1
1
2
3
3
3
HeadCount for each period is calculated as CountOfAssignments(AssignmentStartDate< PeriodStartDate
AND
(AssignmentEndDate is null or AssignmentEndDate >=PeriodEndDate))
Hello v-chuncz-msft,
Here is sample Data Model for HeadCount Report.
AssignmentTable
Id
AssignmentStartDate
AssignmentEnddate
1
12/17/2014
02/15/2015
2
01/05/2015
null
3
01/12/2015
04/30/2015
4
01/21/2015
04/23/2015
5
02/16/2015
null
6
02/23/2015
07/31/2015
Period Table
Year
Period
PeriodStartDate
PeriodEndDate
Date
2014
13
11/24/2014
12/21/2014
12/17/2014
2015
1
12/22/2014
01/18/2015
01/05/2015
2015
1
12/22/2015
01/18/2015
01/12/2015
2015
2
01/19/2015
02/15/2015
01/21/2015
2015
3
02/16/2015
03/15/2015
02/16/2015
2015
3
02/16/2015
03/15/2015
02/23/2015
HeadCount Report for 2015:
Period
Beginning HeadCount
1
1
2
3
3
3
HeadCount for each period is calculated as CountOfAssignments(AssignmentStartDate< PeriodStartDate
AND
(AssignmentEndDate is null or AssignmentEndDate >PeriodEndDate))
Thank you
