Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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 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.
Regards,
Chethan K
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 ] )
)
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.
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
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
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.
I didnt get can you please create a sample file & can you send me if u dont mind.
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)
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
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
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
@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
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |