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

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