Forum Discussion
Slowly changing dimension
Anyone got a good example for this?
Employee | Start Date | End Date |
A | 01/01/15 |
|
B | 01/03/15 | 01/05/15 |
C | 17/06/15 |
|
I want to know for each date, how many employees there are working within a company. Based on above table.
On 31/12/2015 i should have 2 employees.
9 Replies
Something like this should work:
Column = COUNTROWS(FILTER(FILTER('enddates', EARLIER('enddates'[Start Date])>'enddates'[Start Date]),ISBLANK([End Date])))+1
Would it be possible to put this into an example? I uploaded an pbix below.
In your solution, i dont see any reference to a calender table. And i don't see how i will get a bar for each day.
Perhaps i should rephrase my question?
i have to dates in a record:
IDStart End 01-01-2015 31-12-2015
I wan
Hi Smoupre,
I couldn't get you.
As i see there a table with column Employee Name , Start Date and EndDate.
But the end user can select any date to know the number of employee on that particular date. So we will reqiure another Date master table containing all dates.
How would you achieve it using the formula you mentioned?
Thanks
Sushant
i got these from @konstantinos
Headcount = COUNTROWS ( FILTER ( Contracts; Contracts[Star Date] <= MAX(Calendar[Date]) && Contracts[End Date] > MAX(Calendar[Date]) ) )
Headcount faster = CALCULATE ( COUNTROWS ( Contracts ); FILTER ( Contracts; Contracts[Start Date] <= MAX ( Calendar[Date] ) ); FILTER ( Contracts; Contracts[End Date] > MAX ( Calendar[Date] ) ) )
Headcount 3 = CALCULATE ( DISTINCTCOUNT( 'Contracts'[EmployeeID]); FILTER ( 'Contracts'; Contracts[Start Date] <= CALCULATE(MAX ( Calendar[Date] ) )); FILTER ( 'Contracts'; Contracts[End Date] >= CALCULATE( MIN( Calendar[Date] ) ) ) )
didn't find the time yet to test
