cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JessicaVP Frequent Visitor
Frequent Visitor

No worked days by month

Hi, 

 

I need to sum the days of absenteeism (Nº of days that employee have not come to work)  per month. My problem is:  if a worker has not come because it has been sick 40 days, these 40 days join to the month of the first day that stopped working (because I have related the calendar with [Date_StopWork] to use in the dashboard as a filter ) and I want that I sum the days corresponding to every month. For example if  stoppes coming 1/1/2010 and went back  9/2/2010 i want to sum  31 days in January and 9 in February.

In the table ABSENCE: I have the columns: [ID_Employee]  [ID_Absence_reason]  [Date_StopWork] [Date_Returned] [Nº_Days_Absence]

Notes: I have tried of doing a calendar with every day and saying if it came or not and sum them. But it does not work because it does not bear in mind if more than one employee cannot have come this day. I If is the case should sum them, because then I compare it with the whole of days that had were work using  Count (ID_Employee*22) (22 is removing the weekends)

 

 

 

Necesito sumar los días de absentismo (no han venido a trabajar) de los trabajadores por mes. Mi problema es que si un trabajador no ha venido porque ha estado enfermo 40 días, esos 40 días se suman al mes del primer dia que dejó de trabajar (porque he relacionado el calendario con Date_StopWork) y yo quiero que sume los dias correspondientes a cada mes. Por ejemplo si dejó de venir 1/1/10 y volvio el 9/2/2010 que sume 31 días en enero y 9 en febrero. 
En la tabla ABSENCE: tengo las columnas: ID_Employee  ID_Absence_reason   Date_StopWork   Date_Returned    Nº_Days_Absence

 

Nota: He probado de hacer un calendario con todos los días y decir si vino o no y sumarlos. Pero no funciona porque no tiene en cuenta que mas de un trabajador puede no haber venido ese día. Que si ese es el caso deberia de sumarlos, porque luego lo comparo con el total de días que se tenian que haber trabajado con Count (ID_Employee*22) (Días del mes quitando lo fines de semana)

 

 


Muchas gracias de antemano, llevo tiempo pensando mucho y no consigo tener una idea clara de como avanzar.

5 REPLIES 5
Super User
Super User

Re: No worked days by month

I suggest you remove the relationship between the calendar table and the data table.  Then write this measure (you may need to change column and table names).  You will also need a work days coloumn in the calender table that contains 1 for a workday and 0 for a non work day.

 

=CALCULATE(sum('Calendar'[Work Day]),
FILTER('Calendar',
'Calendar'[Date] >= FIRSTDATE(Data[Date_StopWork]) &&
'Calendar'[Date] < LASTDATE(Data[Date_Returned])
)
)



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
JessicaVP Frequent Visitor
Frequent Visitor

Re: No worked days by month

Hi  -  thanks for the response. 


But, how I get a work days coloumn in the calender table that contains 1 for a workday and 0 for a non work day for each employee? 


Thanks in advance!
Jéssica.

 

Super User
Super User

Re: No worked days by month

Calendar tables are normally created by you. You could do it in power query, Excel, or maybe even find one online from Azure Market place or somewhere else. Weekends are easy, public holidays are a bit harder. Frankly I who dprobably do this with power query.   I wrote an article a couple of years ago about how to do it. https://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

 

I don't think I covered working days - might be time for a follow up article. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
JessicaVP Frequent Visitor
Frequent Visitor

Re: No worked days by month

Thanks for the reply. I really appreciate your contribution.

 
But I still can't make progress, because I don't know how to related the data table (used for filter) with the absence table. 
And I don't know how to make a colum with the workdays and non workdays because my non workdays are when employees are sick and it can be one day as 3 months. Is not related with weekends or holidays.

I'm try creating colums with the EndOfMonth[Date_StopWork] and EndOfMonth[Date_Returned] and if it's the same apply the formula If not  apply the formula change [Date_StopWork] for StartOfMonth[Date_Returned] but the employee who was sick 3 or more months the intermediate months don't take account.

And once solve the intermediate months' problem how to sum these days in his respective months and related with de calendar.  Or any other ideas that I can try?


Thanks in advance!
Jéssica.

Super User
Super User

Re: No worked days by month

My formula assumes you have 1 data table containing the absence data and a calendar table, and these tables are not joined. When I say "working days" I am not talking about they days an employee works, I am talking about the days the buisness operates. Assuming your buisness is closed on the weekend, if someone is sick on Friday and returns Monday, that is 1 day off. But it is 3 elapsed days. You need a column in the calendar table indicating "normal work days" to be able to manage the difference between elapsed days and elapsed working days.

 

If you have more data tables (you say you have a data table and an absence table, then it may need to be different. But it is impossible to say without understanding the entire data model (every table and its purpose, every relationship). Thi sof course makes giving advice more difficult. What I was hoping was to give you the idea and hopefully you could work it out for your model. 

 

So the idea is to take your absence table (disconnected from the calendar table), use the stop and start dates to filter the calendar table, then once the calendar table is filtered, you add up the "normal working days" column to see how many working days they have been off work. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 154 members 2,065 guests
Please welcome our newest community members: