Forum Discussion
Graph with sum line
- 9 years ago
This is a classic problem that I have seen a few times, the solution is to create an "active employees" measure with a disconnected Calendar.
To do this you will need a Calendar table with contigeous days in it.
The fastest way to do this is create a new table
Calendar = CALENDARAUTO()... this will scan your data and build table with a list of dates. I recommend you build a proper date dimension but this does the trick.
This Calendar[Date] is used to slice the data, no relationships are needed as we build a measure to lookup the value.
Then your employee count measure:
Employees = DISTINCTCOUNT(Table1[EmployeeID])
Then you need a measure that counts the employees whose "admission date" is before (<=) the last date in the selected period and "termination date" is after the first date in the selected date period
CALCULATE([Employees], FILTER(Table1, (Table1[Admission Date] <= LASTDATE (Calendar[Date])) && (Table1[DismissalDate] >= FIRSTDATE(Calendar[Date]))))
Hi joubertsaquett
I think you just need to use ALL to remove the filters and show the total.
Measures:
Admissions = SUM(Table1[Admissions])
All admissions = CALCULATE([Admissions],ALL(Table1))
This will always return the total admissions from all data/time
- joubertsaquett9 years agoFrequent Visitor
Thank you, I'll take the test!