Counting days off per person

[Anonymous]

Hello all.

I have a data-set that looks like the below. I have labelled the table names (obviously the real data-set is much bigger).

I have the absences for each user, on a per month basis. 

What I need to do is be able to track working days per month per person over time using a piece of DAX - can anyone help please?

Thanks

Adam

[amitchandak · Accepted solution]

Assuming the absence table has month year and only one record per month per employee.

You can get no of working days in  the absence  table

Working days = countx(filter(datetable,datetable[Is Working Day] ="TRUE" && format(datetable[date],"MMMM-YYYY")=absence[month]),datetable[date])

[v-eachen-msft]

Hi @Anonymous ,

You could refer to the following DAX:

Measure =
CALCULATE (
    COUNT ( 'Date Table'[Date] ),
    ALLSELECTED ( 'Date Table' ),
    FILTER ( 'Date Table', 'Date Table'[Is Working Day] = TRUE () )
)
    - CALCULATE (
        SUM ( 'Absences Table'[Days Off] ),
        USERELATIONSHIP ( 'User Table'[Name], 'Absences Table'[Name] )
    )

[mwegener · Accepted solution]

Hi @Anonymous ,

check this quick an dirty PBIX 😁

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[amitchandak · Accepted solution]

Assuming the absence table has month year and only one record per month per employee.

You can get no of working days in  the absence  table

Working days = countx(filter(datetable,datetable[Is Working Day] ="TRUE" && format(datetable[date],"MMMM-YYYY")=absence[month]),datetable[date])