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
fbrossard
Advocate V
Advocate V

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. 

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)

Hi @jleibowitz,

 

I am not sure if you have got your answer or not.

Here is the answer, output for below formula will be either 1 or 0.

 

If you are planning to get total no. of days then you have to use COUNTROWS or SUM function in your main field.

 

eg: Nofworkingdays_Month1= CALCULATE(

SUM('Dimtable'[.isworkingday]),

Filter(

All(Dimtablename), 'dimtable[date].[MonthNo] = Month(MAX(newtable[date]))

&&  'dimtable[date].[Year] = YEAR(MAX(newtable[date])) ))

 

or

Nofworkingdays_Month2 = CALCULATE(COUNTROWS('Lastmonth'), Dimtable[.IsworkingDay] =1)

 

For more details refer to this link: https://blog.andredevelopment.com/post/power-bi-desktop-fiscal-comparison-part-1-setting-up-for-the-...

 

Cheers, Archie

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
)

Anonymous
Not applicable

This is really helpful to calculate SumX and it works but when I try to calculate average TAT, it thows an error.

Can anyone please help me to find average TAT for the same problem.

 

Thanks in advance for your help..!!

Anonymous
Not applicable

This is really helpful to calculate SumX and it works but when I try to calculate average TAT, it thows an error.

Can anyone please help me to find average TAT for the same problem.

 

Thanks in advance for your help..!!

Hi There.

 

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

 

IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1) - Error on "Cannot find table 'Calendar'.

 

This procedure looks perfect for my needs just rying to get it to work.

 

Thanks for posting.

Anonymous
Not applicable

I need to follow this post as I am now having the same issue with one of my Power BI reports.  I have a starting date (creation) and an ending date (response) and I need to count the number of WORKING DAYS (minus a possible holiday).  I tried to follow the same example and created a measure of Calendar = CALENDAR(DATE(2012,01,01),DATE(2020,12,31)) and then
IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1), but I get the same error about 'Calendar' not found.  I am thinking that Calendar needs to be a table by itself?

 

I did some testing and yes, create a NEW TABLE using that first DAX and then IsWorkingDay would be a measure to that 'Calendar' table.  Then the OverallTAT DAX formula is for your main table.  Now I need to figure in weeday holidays and then I will be golden.

I have just been trying to work through this. the Is Work day is a new column and not a measure. I was having the same problem then the penny dropped

Hello There.

 

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

 

IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1) - ERROR = Cannot find table 'Calendar',

 

This looks to be exactly what I need, I just need to get the measures working.

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)

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

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]
)

Anonymous
Not applicable

I got a situation that I have to set another filter to the function and I don't know how to do that.
I need to get the work days from CalendarTable but just the last 15 days(I call it "cycles") of the last month from Table1.

So the months have 2 cycles and each cycle has an ID.

 

October:

cycle 1: TableDate[CycleID] = 391, Table1[start_date] = 01/10/2018 (DD/MM/YYYY) , Table1[end_date] = (15/10/2018)

cycle 2: TableDate[CycleID] = 392, Table1[start_date] = 16/10/2018 (DD/MM/YYYY) , Table1[end_date] = (31/10/2018) *last day depends on the month

 

I created a Switch formula in calendarTable to flag the cycles, a relationship with a table of IDs and it's ok.

 

If I put the formula into a table visual with the cycles on columns it works, but I want the number of work days just for the last cycle in a Visual Card that shows just 1 value.

 

WORKDAYS = SUMX (
    FILTER (
        DateTable;

        DateTable[Date] >= MIN(Table1[START_DATE])

        && DateTable[Date] <= MAX(Table1[END_DATE]) 
        && DateTable[CycleID] = MAX(Table1[CYCLEID])); => it doesn't work

DateTable[IsWorkingDay]

)

 

Need some help.

Thanks.

Anonymous
Not applicable

 

 

 

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

 

 

 PBI issue.PNG

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.

 

 

If I can...

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.