Count Data in the course of time

[Lisa_G]

Hi,

every employee in our company has to make 50 suggestions for improvement (CIP) per year respectively one per week. Our goal is to make a  line chart where we can see the cumulated goal and the total cumulated CIP`s per FTE or per Unit (e.g. Department Accounting). We want to consider new hires and/or employees that are leaving.

 Based on the table below we have drawn the cart in Excel:

 

We have this data: name of employee, departement of employee, entry and leaving date, every CIP with the submission date(we can count) and the target of 50 CIP´s per year/FTE.

Example data:

 

Best regards,

Lisa

[v-caliao-msft]

@Lisa_G,

I have tested it on my local environment, the steps below are for you reference.

1. Create a Weeknumber table.
   Table = SUMMARIZE(CIP,CIP[CSubmissionWeek],"WeekTotal",CALCULATE(SUM(CIP[CIP]),ALLEXCEPT(CIP,CIP[CSubmissionWeek])))
2. Create a calculated column by using the DAX expression below.
   EmployeeCount = CALCULATE(COUNT(Employee[EmployeeID]),FILTER(Employee,Employee[EntryWeek]<='Table'[CSubmissionWeek]&&Employee[LeavingWeek]>='Table'[CSubmissionWeek]))
3. Create two running total measure.
   RunningtotalSuggestions = CALCULATE(SUM('Table'[WeekTotal]),FILTER(ALL('Table'),'Table'[CSubmissionWeek]<=MAX('Table'[CSubmissionWeek])))
   RunningtotalOneEmployeeOneSuggestionPerWeek = CALCULATE(SUM('Table'[EmployeeCount]),FILTER(ALL('Table'),'Table'[CSubmissionWeek]<=MAX('Table'[CSubmissionWeek])))

Sample table

Results

Regards,

Charlie Liao

[Lisa_G]

Hi Charlie Liao,

thank´s for your help!

The column EmployeeCount don´t give me the right figure. Maybe because I have entry dates and leaving dates over years and not only for 2017? -> We have to combine week and year of the dates.

The result is a fixed chart. I can´t use e.g. a basic filtering by the employee because the measures have no connection to this informations. E.g. I want to drill down the chart to see how many CIP´s employee 1001 made in week 1.

Do you know a way to solve this?

Regards,

Lisa