Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
lennox25
Post Patron
Post Patron

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
Anonymous
Not applicable

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.

vpollymsft_0-1657591670835.png

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.

vpollymsft_1-1657591781222.png

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

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

vpollymsft_2-1657591876649.png

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.

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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.

vpollymsft_0-1657591670835.png

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.

vpollymsft_1-1657591781222.png

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

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

vpollymsft_2-1657591876649.png

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.

pfarias
Advocate I
Advocate 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

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.

Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])

 

@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 )
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

 

amitchandak
Super User
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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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

lennox25_0-1657207925499.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.