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 Number||Opened Date/Time||Closed Date /Time||Days Duration|
|806452859||9/30/2015 14:39||10/19/2015 12:22||14|
|806452860||10/20/2015 17:28||10/22/2015 10:38||3|
|806452861||5/20/2015 16:13||5/27/2015 10:29||6|
|806452862||11/3/2015 11:47||11/5/2015 11:32||3|
|806452863||8/18/2015 17:05||9/18/2015 12:23||24|
|806452864||4/20/2015 13:18||4/23/2015 14:22||4|
|806452865||10/1/2015 12:26||10/5/2015 11:08||3|
|806452866||4/1/2015 2:04||4/23/2015 16:24||17|
|806452867||11/23/2015 12:28||12/28/2015 12:27||26|
|806452868||11/23/2015 10:53||11/30/2015 18:06||6|
|806452869||4/23/2015 17:22||4/29/2015 11:02||5|
|806452870||4/23/2015 12:58||4/27/2015 10:09||3|
Thanks in advance.
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
Step 2: Create Days Duration excluding Weekends by creating another calculated column in your orders table
Days Duration excluding Weekends=CALCULATE(SUM(Dates[IsWorkDAY]),
OrdersTable[Opened Date/Time ],
OrdersTable[Closed Date /Time ] )
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.
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)
@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.
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