Forum Discussion
No worked days by month
Community ChampionI 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])
)
)
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.
- MattAllington
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.
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.- MattAllington
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.