cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smatesic Regular Visitor
Regular Visitor

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

Accepted Solutions
smatesic Regular Visitor
Regular Visitor

Re: Number of working days

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. 

14 REPLIES 14
fbrossard Member
Member

Re: Number of working days

smatesic Regular Visitor
Regular Visitor

Re: Number of working days

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. 

gpiero Member
Member

Re: Number of working days

Hi All,

first of all I apologize if I drop in this post in this way.

I am a begineer in Power BI and in this forum too and I am trying to apply the formula below.

 

My data come from a  Sharepoint list and I have noticed two issues to apply this formula:

First, my list contains the Starting Date and Ending DAte in a row. I can check  if both are NOT Saturday or Sunday.

But how the formula will check the data between, since they could belong to different weeks or months?

 

Second I do not have the table Holidays there. Obviously I could create my list there but I would not do it.

 

Then I have created a new table using Enter Data (I am not using a EN version, so I am not sure  the menu item is correct) in my Power BI desktop model and I added the sequence of my country holidays.

 

After saved it, I realized I did not add some extra company vacation. How can I  add some more rows to the table Holidays?

 

Thanks a lot for your feedback.

 

 

arunbantiya Frequent Visitor
Frequent Visitor

Re: Number of working days

 

 

 

I am not getting the result as expected. Am I missing something? should the Sheet1 and sheet2 be related?

 

 

 PBI issue.PNG

Surendra_thota Regular Visitor
Regular Visitor

Re: Number of working days

Hi Smatesic

 

how to achieve if we have list of holidays  in the form of ranges in holiday table  like startdate and enddate  instead of one column

Highlighted
zornstv Frequent Visitor
Frequent Visitor

Re: Number of working days

I'm using the following syntax to calculate working days.   How do I handle null values or dates missing.

 

Production TAT =
SUMX (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN ( 'Order'[date_ProductionScheduled] )
            && 'Calendar'[Date] <= MAX ( 'Order'[date_ProductionDone] )
    ),
    'Calendar'[IsWorkingDay]
)

leibowjb Frequent Visitor
Frequent Visitor

Re: Number of working days

Hello,

 

I am trying to calculate the number of working days. All days except sunday and the dates listed in the 'Holiday Dates' table should count as a working day. However, all of my dates are being assigned a value of 0. Am I missing something? Thanks!!

 

IsWorkingDay = IF (NOT(DimDate[Day]= "Sunday") && COUNTX(RELATEDTABLE('Holiday Dates'),1)<1,1,0)

leibowjb Frequent Visitor
Frequent Visitor

Re: Number of working days

Hello,

 

I am trying to calculate the number of working days. All days except sunday and the dates listed in the 'Holiday Dates' table should count as a working day. However, all of my dates are being assigned a value of 0. Am I missing something? Thanks!!

 

IsWorkingDay = IF (NOT(DimDate[Day]= "Sunday") && COUNTX(RELATEDTABLE('Holiday Dates'),1)<1,1,0)

zornstv Frequent Visitor
Frequent Visitor

Re: Number of working days

You will need to create some new measures.  See Below.  This is for Monday thru Friday workweeks

 

Calendar = CALENDAR(DATE(2012,01,01),DATE(2020,12,31))

 

IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1)

 

This is what I use to calculate turnaround time as a new measure.   The above measure need to be created first.

 

Overall TAT =
SUMX (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN ( 'Order'[date_Creation] )
            && 'Calendar'[Date] <= MAX ( 'Order'[date_OrderShipped] ) && NOT( ISBLANK(MAX ( 'Order'[date_Creation] )))
    ),
    'Calendar'[IsWorkingDay]

 

 

Hope this helps.   

Steve
)