locked
PowerPivot - Help with DatesBetween Function to Create Running Headcount Total RRS feed

  • Question

  • Hello Everyone,

    I am trying to create a measure that shows a running headcount total at any given time.  I created an [EmployeesAdded] Calculation that shows how many employees were added for each date.  Now, I'm trying to create a calculated value to show headcount at any given date based on the amount of employees added.  So, I tried the following formula:

    HeadCount:=CALCULATE(COUNT([EmployeesAdded]),DATESBETWEEN([DateKey],FIRSTDATE(all([DateKey])),LASTDATE([DateKey])))

    However, it keeps giving me the following error: 'Semantic Error - DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument.'  I'm confused as to why it gives me this error since [DateKey] is a date column reference.  I have also tried a [FullDate] column with the same results.  

    Any help on helping me understand why I got this error, or help in figuring out how to calculate total cumulative headcount on any given date would be greatly appreciated.

    Thank you!

    EmployeesAdded:=CALCULATE(count('Original Date of Hire'[Original Date of hire])-count('Termination Data'[Last Day of Employment]))

    Monday, June 17, 2013 7:23 PM

Answers

  • Use FullDate and include the TableName with the column name.  In a calculated measure like this, you need to use fully-qualified column (TableName[ColumnName]) with the date functions as well as ALL.

    Like this:

    HeadCount:=CALCULATE(COUNT([EmployeesAdded])
    ,DATESBETWEEN(DimDate[FullDate]
    ,FIRSTDATE(ALL(DimDate[FullDate]))
    ,LASTDATE(DimDate[FullDate])
    )
    )

    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Tuesday, June 18, 2013 5:19 AM
    Answerer

All replies

  • Hi Dan Tip,

    You try to use [FullDate] instead of [DateKey].

    Regards,

    Tuesday, June 18, 2013 2:11 AM
  • Use FullDate and include the TableName with the column name.  In a calculated measure like this, you need to use fully-qualified column (TableName[ColumnName]) with the date functions as well as ALL.

    Like this:

    HeadCount:=CALCULATE(COUNT([EmployeesAdded])
    ,DATESBETWEEN(DimDate[FullDate]
    ,FIRSTDATE(ALL(DimDate[FullDate]))
    ,LASTDATE(DimDate[FullDate])
    )
    )

    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Tuesday, June 18, 2013 5:19 AM
    Answerer