Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amien
Helper IV
Helper IV

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 9
Greg_Deckler
Super User
Super User

Something like this should work:

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@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

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
Resident Rockstar
Resident Rockstar

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.

 

 

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

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

 

 

Perhaps i should rephrase my question?

 

i have to dates in a record:

 

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

 

I wan

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.