cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Re: DATEDIFF Working Days

I can no see where is the calculation to find the weeks days with the scenario using date from 2017/1/1 to 2017/3/31.

Highlighted
Helper I

## Re: DATEDIFF Working Days

Hello @v-huizhn-msft, this is a great solution and I"m close to solving my problem, but how can I insert the measure result as a value in the new column? I need the business days value for EACH ROW of data, not as a single summarized measure. Any ideas?

Highlighted
Helper II

## Re: DATEDIFF Working Days

Hi,

I stuck at this step too.  How do I use this measure result in calculating the column?

Highlighted
Frequent Visitor

## Re: DATEDIFF Working Days

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

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

for example: Nofworkingdays_Month = CALCULATE(COUNTROWS('month'), Dimtable[if working day] =1)

where, Month  = DATESINPERIOD('Calendar'[Date],[ThisMonth],1,MONTH)

ThisMonth = DATE(YEAR([Today]), MONTH([Today]),1)

Today = DATE(YEAR(Now()), MONTH(NOW()), DAY(NOW()))

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

Highlighted
Frequent Visitor

## Re: DATEDIFF Working Days

Please find my soultion below for finding no. of working days excluding holidays & weekends.

WorkingDays_FiscalMTH = CALCULATE((sum('Calendar'[.IsWorkingDay])+1),ALLEXCEPT('Calendar','Calendar'[fiscalYear],'Calendar'[fiscalQuarter],'Calendar'[fiscalMonth]))

OR

Use

WorkingDays_MTH = CALCULATE((sum('Calendar'[.IsWorkingDay])+1),ALLEXCEPT('Calendar','Calendar'[Year],'Calendar'[Quarter],'Calendar'[Month]))

Note: Assuming that you already had a calendar table with Fiscal year, Fiscal Quarter & Fiscal Month.

Hope this helps!!

Cheers,

Archie

Highlighted
Anonymous
Not applicable

## Re: DATEDIFF Working Days

Good morning,

I've followed these instructions for calculate the working days. In fact, I have a table with the saturdays, sundays and holidays. But I'm not getting what I want. Could you help me?

Also, I have a table like this:

 ID Start Date Final Date DATEDIFF DATEDIFF1 123 01/01/2018 02/01/2018 1 ? 124 05/01/2018 10/01/2018 5 ? 125 15/01/2018 01/02/2018 17 ? 126 18/01/2018 20/01/2018 2 ?

I got a DATEDIFF between dates but I want the DATEDIFF discounts the saturdays, sundays and holidays. I thoght about calculate how many saturdays, sundays and holidays are between start and final date and then rest it in DATEDIFF column. But I don't know how I should say it to PowerBI.

Regards

Highlighted
Anonymous
Not applicable

## Re: DATEDIFF Working Days

@v-huizhn-msft wrote:

Hi @Benjamin_500,

First you should create a holiday table including all holiday form Delivery start date to Today. Then create relationship between holiday table and your 'Repot' table.

I try to reproduce your scenario using date from 2017/1/1 to 2017/3/31.

Search the holiday days for 2017, I use "List of federal Public Holidays of USA in 2017", and type it in Power BI table as followings.

Create relationship between Holiday table and Date Table.

Use WEEKDAY function to get weekdays, use RELATED function to get holidays. Use if function to decide if the day is work day.

```WeekDay = WEEKDAY(DateTable[Date])

Holiday = RELATED(Holiday[Holiday])If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))```

The 1 stands for the day is work day.

Finally, create a measure to calculate the number od holidays. And create a card visual to display the result.

`Total work days = CALCULATE(COUNT(DateTable[Date]),FILTER(DateTable,DateTable[If work day]=1))`

If you have other issues, please let me know.

Best Regards,
Angelia

Hi @v-huizhn-msft Angelia,

I'm having the same issue, your solution is great, however this will give the total result for an year. how to obtain work days per month basis. i.e.

January  20

Feb        19

Mar        21    likwise. I'm bit strugglling with dax code. Thanks again. Keep up the good work!

Highlighted
Regular Visitor

## Re: DATEDIFF Working Days

Hi,

The problem is, this solution will not work if you want to see the difference between two timestamps and want to exclude the working days between them

One probable solution as a temporary workaround is just taking

1. The difference between two dates using Datediff and
2. Subtracting this by creating another calendar which can provide the number of holidays between the dates.

I think PowerBI should come up with Networkdays like excel 365 to make life easier for users

Thanks

Anand

Highlighted
Resolver III

## Re: DATEDIFF Working Days

If you want to count the number of working days between two dates...just SUM the "Is Workday" column of your date table between the dates you want.

Ex)

Date     |     Is Workday

1/1/19          0

1/2/19          1

1/3/19          1

1/4/19          1

1/5/19          1

1/6/19          1

1/7/19          0

Highlighted
New Member

## Re: DATEDIFF Working Days

Excellent explanation, I have two questions,

1. How to do in the case of having holidays from various countries?,

Thinking in a multinational company. Having several countries we could no longer make the 1: 1 relationship between the Date table and the Holiday table because the dates would be repeated when there are different holidays on the same day. Therefore the RELATED function could no longer be used.

2. And how to add the vacations of each employee (who live in different countries)?

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors