cancel
Showing results for
Did you mean:
Highlighted
Member

## Date Duration exclude weekends

Hi All,

i need a DAX formula to calculate the Date Duration by Excluding the Weekends (Saturday & Sunday) Below is the Table View.

TableName : WeeklyReport

 Order  Number Opened Date/Time Closed Date /Time Days Duration 806452859 9/30/2015 14:39 10/19/2015 12:22 14 806452860 10/20/2015 17:28 10/22/2015 10:38 3 806452861 5/20/2015 16:13 5/27/2015 10:29 6 806452862 11/3/2015 11:47 11/5/2015 11:32 3 806452863 8/18/2015 17:05 9/18/2015 12:23 24 806452864 4/20/2015 13:18 4/23/2015 14:22 4 806452865 10/1/2015 12:26 10/5/2015 11:08 3 806452866 4/1/2015 2:04 4/23/2015 16:24 17 806452867 11/23/2015 12:28 12/28/2015 12:27 26 806452868 11/23/2015 10:53 11/30/2015 18:06 6 806452869 4/23/2015 17:22 4/29/2015 11:02 5 806452870 4/23/2015 12:58 4/27/2015 10:09 3

Regards,

Chethan K

10 REPLIES 10
Super User

## Re: Date Duration exclude weekends

Hi Chethan,

You should import the Dates Table in your data model for the implementation of my solution.

Step 1: As Part of the calculation, Create IsWorkDay Calculated Column in Your Dates Table

IsWorkDay=SWITCH(WEEKDAY([Date]),1,0,7,0,1)

Step 2: Create Days Duration excluding Weekends by creating another calculated column in your orders table

Days Duration excluding Weekends=CALCULATE(SUM(Dates[IsWorkDAY]),
DATESBETWEEN(Dates[Date],
OrdersTable[Opened Date/Time ],
OrdersTable[Closed Date /Time ] )
)

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Member

## Re: Date Duration exclude weekends

Thanks for replay.

Thanks

Regards,

Chethan K

Member

## Re: Date Duration exclude weekends

Instead of summing the [Date] column in your table you should sum your newly created column [IsWorkDay]

*Edit* - It also looks as you have created the [IsWorkDay] in your fact table instead of in the date calendar table. Take a closer look to the proposed solution in the first reply.

Br,

Magnus

Member

## Re: Date Duration exclude weekends

I didnt get can you please create a sample file & can you send me if u dont mind.

Member

## Re: Date Duration exclude weekends

Here is a very simple model that shows same scenario as you have and you can see all calculations I have made to make it work. Please note that the function DatesBetween will return a table that includes both the starting date and the ending date! Thus you need to consider how you define the duration (with starting/ending date included or vice versa)

Create a column with 1 for weekdays and 0 for weekendscalculate number of days from starting date until ending date without weekends

Super Contributor

## Re: Date Duration exclude weekends

@chethan

If you also want to exclude non-working hours, you can take a look at this thread, and transfer the hours to days.

Best Regards,

Herbert

Member

## Re: Date Duration exclude weekends

Hi @magsod ,

For which field (OpenDate or ClosedDate)  i have to create a column with IsWeekday function

Here we have only two date fields OpenDate  &  CloseDate

Member

## Re: Date Duration exclude weekends

@ssvr This proposed solution is based on the assumption that you have a separate date table. And it's for that date table you create the IsWeekday column, not for the OpenDate or the CloseDate in your fact table.

Br,

Magnus

Member

## Re: Date Duration exclude weekends

Hi @magsod

I just followed your steps and i created the Dates (Calender function) & Is work day (your screenshot function)

i created a column with number of weekday function

Out is wrong ??

i enclosed the screen shot for your reference