DAX: Count number of people taking more than 10 days annual leave per year

[Dan80]

Hi,

I have payroll data for the last 2 years for each payrun (every 2 weeks).  What I need to do is sum up all annual leave and then count the number of employees that have taken more than 10 days annual leave in one year.  I can't seem to use 'COUNT' function as this only relates to a column and the data I have is at a more granular level i.e. every 2 week payrun and so needs to be consolidated to an annual period first and then 'Counted'. 

Any ideas/suggestions would be greatly apprecaited.  Thanks and keep Trumping.....

Dan 

[v-sihou-msft · Accepted solution]

@Dan80

In your scenario, to calculate count of employees taking more than 10 days leave within a year, you should have a "attendence" table which contains the Year, bi week and employee columns. I assume you already have a status column to tag the employee is "On" or "Off". You can calculate count of leave within a year or bi week. Then create a calculate table to build the "count of leave within a year" into a column, and count the users.

Please refer to my below:

1. Create a column to give the "Off" days numeric value.

Column = IF(Table2[Status]="Off",1,0)

2. Create "Count Of Leave within Year" measure.

Count Of Leave within Year = CALCULATE(SUM(Table2[Column]),ALLEXCEPT(Table2,Table2[Employee],Table2[Year]))

3. Then new a calculated table to build the "Count Of Leave within Year" into column.

Table = FILTER(ADDCOLUMNS(SUMMARIZE(Table2,Table2[Year],Table2[Employee]),"count of leave",Table2[Count Of Leave within Year]),[count of leave]>=2)

4. Create the "count of employees" measure.

Count employees = CALCULATE(COUNTA('Table'[Employee]),ALLEXCEPT('Table','Table'[Year]))

Regards,

[Dan80]

Wowser, that's impressive, thanks heaps for your help with this -appreciate it!

[alanhodgson]

Hey @Dan80,

Please post some sample or fake data so we can get a better understanding of your data structure and what you are trying to do.

Thanks,

Alan