cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
chethan Member
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

 

Please help on this 

 

Order  NumberOpened Date/Time Closed Date /Time Days Duration
8064528599/30/2015 14:3910/19/2015 12:2214
80645286010/20/2015 17:2810/22/2015 10:383
8064528615/20/2015 16:135/27/2015 10:296
80645286211/3/2015 11:4711/5/2015 11:323
8064528638/18/2015 17:059/18/2015 12:2324
8064528644/20/2015 13:184/23/2015 14:224
80645286510/1/2015 12:2610/5/2015 11:083
8064528664/1/2015 2:044/23/2015 16:2417
80645286711/23/2015 12:2812/28/2015 12:2726
80645286811/23/2015 10:5311/30/2015 18:066
8064528694/23/2015 17:224/29/2015 11:025
8064528704/23/2015 12:584/27/2015 10:093

 

Thanks in advance.

Regards,

Chethan K

10 REPLIES 10
Super User
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.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
chethan Member
Member

Re: Date Duration exclude weekends

Hi @BhaveshPatel

Thanks for replay.

 

 

I Have Created a Dates Table in your data model But its not working please help me.. Below is the screenprint

 

 

1.JPG2.JPG3.JPG

 

Thanks 

 

Regards,

Chethan K

 

magsod Member
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

chethan Member
Member

Re: Date Duration exclude weekends

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

magsod Member
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)

Dates.PNGCreate a column with 1 for weekdays and 0 for weekendsNumbWeekDays.PNGcalculate number of days from starting date until ending date without weekends 

v-haibl-msft Super Contributor
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

ssvr Member
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

magsod Member
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

ssvr Member
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 referenceDDiff2.JPGDDiff2a.JPGDDiff2b.JPG

 

Please help me out this

 

Urgent requirement