Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Something like this should work:
Column = COUNTROWS(FILTER(FILTER('enddates', EARLIER('enddates'[Start Date])>'enddates'[Start Date]),ISBLANK([End Date])))+1
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
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:
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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |