Forum Discussion

chethan's avatar
chethan
Resolver III
9 years ago

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    Ord...
BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

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

nadirS's avatar
nadirS
Helper I
5 years ago

is there a way i can do a reversal on the same thing that you explained above - I have a date table and i am able to calculate working days.(0s for weekends and 1's for Weekdays). I need to add 5 days to my start date and and pick the appropriate working date from the date table so that it gives me an "Expected Completion Date" that takes account of weekends.