Forum Discussion

Icon for Helper III rank

Helper III

6 years ago

Number at a particular date

Hi, I have the following data on headcount. Name Position Start Date End Date Current Status Joe Bloggsd Head of Marketing 21/02/2017 Established Noah Anyone Head of Opera...

Anonymous

Not applicable

6 years ago

You need a date dimension, look at my suggestion here

https://community.powerbi.com/t5/Desktop/measure-to-count-how-many-tasks-a-rep-completed-in-a-given-week/m-p/943020#M451852

tobiasmcbride
Helper III
6 years ago
That won't work as it needs to be able to have a particular date and then say on that date how many did we have in the firm (i.e. whose start date and end date, latter if applicable coincided with it).
- Anonymous
  Not applicable
  6 years ago
  ok in any case add a date dimension and add a slicer on the top with the date
  
  Now you have your date you want to analyze with
  
  CurrentDate=SELECTEDVALUE(YourDateTable[Date])
  
  Now your measure will be
  
  EmployeesAtThatDate= Var CurrentDate=SELECTEDVALUE(YourDateDim[Date]) RETURN COUNTROWS( FILTER(Employees;Employees[StartDate]<=CurrentDate && Employees[EndDate]>=CurrentDate))
  
  As I'm writing on the fly and I don't remember you mihgt have to use
  
  EmployeesAtThatDate= Var CurrentDate=SELECTEDVALUE(YourDateDim[Date]) RETURN CALCULATE(COUNTROWS(Employees);FILTER(Employees;Employees[StartDate]<=CurrentDate && Employees[EndDate]>=CurrentDate))