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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 

  • ssvr's avatar
    ssvr
    Helper III
    8 years ago

    Hi Anonymous ,

     

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

     

    Here we have only two date fields OpenDate  &  CloseDate

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
    ssvr
    Helper III
    8 years ago

    Hi Anonymous

     

    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

     

    Please help me out this

     

    Urgent requirement

  • leticiasf's avatar
    leticiasf
    New Member
    8 years ago

    Hi,

    Thanks so much for all this valuable inputs, I'm trying to use it, however it is not working for all the cases, let me show... 

    Highly appreaciate any help!

     

    I'm using:

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

     

    Days Duration excluding Weekends = CALCULATE(SUM(calendario[IsWorkDAY]);DATESBETWEEN(calendario[Date];'Raw Data'[INCIDENT Submit Date];'Raw Data'[INCIDENT Closed Date]))

     

    Below some examples of wrongly 'Days Duration Excluding Weekends' calculation:

     

    Thanks