Forum Discussion

amien's avatar
amien
Helper V
10 years ago

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...
Need Help
Tutorial Requests
sushantjain's avatar
sushantjain
New Member
10 years ago

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

amien's avatar
amien
Helper V
10 years ago

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