Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smatesic
Helper I
Helper I

Number of working days

I need to calculate number of working days. I have table with employees and their work log - dates on which they worked and duration of work done per day. In other tables I have dates for when they went on vacation, dates for holidays in my country, and dimtable. I want to do something like this:

WorkingDays= Worklog[Date] - VacationDays[Date] - Holiday[Date] (but not if they fall on weekend) - IsWeekend[True/False format]

 

Of course, that is nowhere near valid formula, I'm just trying to show how it's supposed to work.

 

I have dates for WorkLog, VacationDays and Holiday and they are connected to DimTable and Employees are connected to DImTable through WorkLog. I calculated IsWeekend = IF(DimTable[WeekName]= "Saturday" || (DimTable[WeekName]= "Sunday"); "True"; "False") and I have them in True/False format.

 

I may have made this too complicated, I've been circuling around this for some time now. 

The end result should show how many days were working days and compare it to the days on which employees actually worked.

 

 

 

 

1 ACCEPTED SOLUTION

I read the article, it was helpful. In PBI I ended up with this formula:

 

IsWorkingDay = IF (NOT(DimTable[WeekName]= "Saturday" || (DimTable[WeekName]= "Sunday")) && COUNTX(RELATEDTABLE(Holidays);1)<1;1;0)

 

IsWorkingDay is in DimTable. First part of formula is saying take every day from Monday to Friday (not Saturday or Sunday), and second part is going into Holidays Table where I have list and dates of holidays. Everything is conducted with IF function (I use IF a lot in PBI). The result is 1 for working days and 0 for holiday or weekend, which I formated to be True/False. 

 

This works perfectly. So, if anybody needs this, it's here. 

View solution in original post

17 REPLIES 17

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.