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 

 

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

12 Replies

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

    • chethan's avatar
      chethan
      Resolver III
      9 years ago

      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

       

       

       

      Thanks 

       

      Regards,

      Chethan K

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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

    • 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.  

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    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