Slowly changing dimension

[amien]

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. 

[Greg_Deckler]

Something like this should work:

Column = COUNTROWS(FILTER(FILTER('enddates', EARLIER('enddates'[Start Date])>'enddates'[Start Date]),ISBLANK([End Date])))+1

[sushantjain]

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]

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

[amien]

@Greg_Deckler

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.

link

[amien]

Perhaps i should rephrase my question?

i have to dates in a record:

ID    Start        End
01    01-01-2015   31-12-2015

I want a record for each day

ID Date
01 01-01-2015
01 01-02-2015
01 01-03-2015
.....
01 30-12-2015
01 31-12-2015

And then for each unique ID

[greggyb]

This isn't a slowly changing dimension, it's a fact table. A dimension describes a fact. A fact is measured and aggregated.

While you might use this table as a dimension to some other fact, when you write measures against it, it becomes a fact table.

Fact tables are aligned with a business process. Your slowly changing dimension may be a dimension to a sales fact. When the process is HR/headcount, then the fact is the employee table.

Your measures and model become much simpler if you restructure your table to be a fact as described in the answer I provided in this other thread.

[amien]

anyone got a working example in powerbi? I read all the links, but i cant get it working

[amien]

Perhaps i should rephrase my question?

i have to dates in a record:

ID    Start        End
01    01-01-2015   31-12-2015

I want a record for each day

ID Date
01 01-01-2015
01 01-02-2015
01 01-03-2015
.....
01 30-12-2015
01 31-12-2015

And then for each unique ID

[amien]

Perhaps i should rephrase my question?

i have to dates in a record:

IDStart        End
01-01-2015   31-12-2015

I wan