Forum Discussion

apatil's avatar
apatil
Frequent Visitor
9 years ago

Adding calculated columns based on lookup table

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 

Need Help

7 Replies

  • TheOckieMofo's avatar
    TheOckieMofo
    Resolver II
    9 years ago

    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.
  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    9 years ago

    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's avatar
      apatil
      Frequent Visitor
      9 years ago

      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.