cancel
Showing results for
Did you mean:
Helper III

## How to calculate date difference excluding weekends - Always a start date but some end dates blank

Hi - I wonder if anyone can help. I need to calculate the date difference excluding weekends. There is always a start date but sometimes the task has not been completed so there is no end date.

Before I was asked to exclude weekends my calculation was  -

Days taken to Complete = if(ISBLANK('Tracker'[Date of first contact ]),BLANK()
, if(ISBLANK('Tracker'[Date decision confirmed ]), BLANK()
, DATEDIFF('Tracker'[Date of first contact],'Tracker'[Date decision confirmed], DAY)
)
)

Does anyone know if this formula can be adjusted?
1 ACCEPTED SOLUTION
Community Support

Hi @lennox25 ,

I have created a simple sample, please refer to it to see if it helps you.

Replace a blank date with today's date.

Click Trandform data>>Trandform>>Replace values.

Then change the column.

= Table.ReplaceValue(#"Changed Type",null,DateTime.LocalNow() ,Replacer.ReplaceValue,{"end date"})

Then the null value will  be filled with present time.

Then create a measure by using @Greg_Deckler 's Net Work Days .

NetWorkDays =
VAR Calendar1 = CALENDAR(MAX(Sheet3[start date]),max(Sheet3[end date]))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

If I have misunderstood your meaning, please provide some sample data and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 REPLIES 11
Community Support

Hi @lennox25 ,

I have created a simple sample, please refer to it to see if it helps you.

Replace a blank date with today's date.

Click Trandform data>>Trandform>>Replace values.

Then change the column.

= Table.ReplaceValue(#"Changed Type",null,DateTime.LocalNow() ,Replacer.ReplaceValue,{"end date"})

Then the null value will  be filled with present time.

Then create a measure by using @Greg_Deckler 's Net Work Days .

NetWorkDays =
VAR Calendar1 = CALENDAR(MAX(Sheet3[start date]),max(Sheet3[end date]))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

If I have misunderstood your meaning, please provide some sample data and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

Depending on your database, if you wish, you can add a column with the difference between dates directly in Power Query.

PatternsM/fnNumberWorkDay.m at main · pietrofarias/PatternsM (github.com)

#PowerQuery - Número de Dias Úteis - YouTube

Helper III

HI @amitchandak I already have differnce between the dates as formua shown in my original question. The formula you provided works perfectly providing there are no blank dates. There are blank dates (end dates) so now the formula shows as error. I have a standard (company) date table and have brought in an excel spreadsheet that gets updated daily  -which contains all the start and end dates.

Super User

@lennox25 See if Net Work Days helps. Net Work Days - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

@Greg_Deckler Thanks, great, but what about factoring in the blank end dates?

Super User

@lennox25 VAR __EndDate = IF(ISBLANK(...), TODAY(), ...).

Basically just substitute today's date if it is blank.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

@Greg_Deckler Hi - I know I may sound dull.. Im still learning.  In Network Days - How do I change the formula to work for me? What is calendar  and calendar 2 and what [Date] field do I use? Thank you

NetWorkDays =
VAR Calendar1 = CALENDAR(MAX(NetWorkDays[created date]),MAX(NetWorkDays[review date]))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

Super User

@lennox25 Try something like this:

NetWorkDays =
VAR __StartDate = MAX(NetWorkDays[created date])
VAR __EndDate = MAX(NetWorkDays[review date])
VAR __EndDate1 = IF(__EndDate <> BLANK(), __EndDate, TODAY())
VAR Calendar1 = CALENDAR(__StartDate , __EndDate1 )
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper III

@Greg_Deckler Thank you - Ive used the fomula and its throwing up all 3s?

Super User

@lennox25 , a new column like

COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Date of first contact],Table[Date decision confirmed]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Helper III

@amitchandak Thank you - via a search I already tried this but as the end date is blank in a few rows it shows an error. Any idea how to factor this in? TIA

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors