Calculate the percentage of abscens

[Anonymous]

Hello! I would like some help with dax. I need to calculate the percentage that is absent. For this I have a table that stores people who have been absent from work:

Date	Person ID	Code for cause of absence	Code for organisation	Period
2021-09-01	123	A	1200	202109
2021-09-01	345	A	1350	202109
2021-09-01	678	B	1250	202109
And so on	And so on	And so on	And so on	And so on

I also have a table with all employees:

Period	PersonID	Code for organisation
202109	123	1200
202109	345	1350
202109	678	1250

I made a measure that calculates how many are absent, and it looks like this: number absent =DISTINCTCOUNT('table_absence'[PersonID]).

I also have a measure that counts all employees. 

Now I want a measure that calculate the percentage that is absent per day, but the problem is that first table is per day and the other one is per month. 

I tried with this but it does not work: 

absence per day (%) = CALCULATE(DIVIDE([number absent];[All_Employees])* 100;employees[period] = SELECTEDVALUE('table_absence'[period]))

For example, 2021-09-01 there were 450 people away, and that month had 5000 employees, so I want it to take 450 / 5000 * 100 

Another problem is that we can only see the number of employees the month after, so if I look at how many are away today, the number of employees would be empty. I would like a IF statements that checks if it is the current month, then take the employees from last month, otherwise take same month. 

Help please 🙂

[v-zhangti · Accepted solution]

Hi, @Anonymous 

Please check the following methods.

number absent = DISTINCTCOUNT('table_absence'[Person ID])

absence per day (%) = 
CALCULATE(DIVIDE([number absent],COUNT(employees[PersonID])),FILTER(ALL(employees),[Period]=SELECTEDVALUE(employees[Period])))

Regarding your other question, you need to provide a little more example data, which I fail to understand at the moment. You can be informed of the output you expect.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.