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

  • 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

      • chethan's avatar
        chethan
        Resolver III
        9 years ago

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

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