Adding calculated columns based on lookup table

[apatil]

Hi All,

I am trying to work on a consolidated report which shows beginning headcount, Newhires and other details for each period and year.

I have lookup values for year, period, start and end date of period in ‘PeriodTable’. And Assignment ID,  Assighnment Start Date,Assignment End Date etc in ‘Assignmnet table’.

I would like to make the aggregations for each period for selected year from period table

I need your suggetions to create a calculated columns/measures for below two conditions as there is no direct relationship bewteen my Period Table and Assignment Table–

1. Begininmg headcount : it should be count of all assighnments whose start date is less than period start date and assignment end date is either null or greater than period start date.
2. New Hire : It is count of all assignments where assignment start date is between period start and period end date

The final report should look like below –

Period	Beginning Head Count	New hires
1	512	21
2	254	24
3	269	12
4	657	9

Any help would be much appreciated.

Thank you 

[v-chuncz-msft]

@apatil,

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
        )
    )

[apatil]

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.

[v-chuncz-msft]

@apatil,

Please share us your simplified model, then we can have a test.

[skoleti]

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

[v-chuncz-msft]

@skoleti,

Just remove the relationship and make period unique.

[skoleti]

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))

[TheOckieMofo]

First off, there are several people that are on the Mt. Rushmore of DAX. One (technically 2) of these people is The Italians. They have a website called DAX patterns that addresses a lot of common issues such as this one. I think you can use this technique to help you:

Cumulative Total

You would have to manipulate it a bit, though. But essentially, you should be able to create two joins between the period table and fact table and create a measure that essentially looks like this:

Cumulative Quantity :=

CALCULATE (

    Count( Assignment_ID ),

    FILTER (

        ALL ( 'Period'[PeriodStartDate] ),

        'Period'[PeriodStartDate] <= MAX ( 'Period'[PeriodStartDate] )

    )

)

I think that should get you the beginning headcount. Hopefully that will get you started.