Forum Discussion
Graph with sum line
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]))))
You can create a measure like:
Measure = CALCULATE(SUM([Value]),ALL(Table))
Then just add this as a Value in your chart.
Thank you for your help,
One more doubt is it possible to perform some calculation (admissions / layoffs) to show the growth of the company in the chart?
That would depend on your data and the format of that data. But, yes, if you have a list of people who have left, then you should be able to perform that calculation. Can you show some example data?
Yes I can,
In this image below, inside the "square" are the fields of the database.
1. date of admission (ex: 20170130)
2. Date of dismissal (ex: 20170130)
3. Payroll situation (D = Dismissed or EMPTY = active)The other fields are "measures"
In this image, it correctly displays the total number of employees I have active today, but it does not change according to the date.
obs. We created measure for dates of admission and dismissal by changing the "field format" to date
If you need more information please let me know,
Thank you for the great help,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]))))