March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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 -
Solved! Go to Solution.
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]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
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.
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]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
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.
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)
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.
@lennox25 See if Net Work Days helps. Net Work Days - Microsoft Power BI Community
@lennox25 VAR __EndDate = IF(ISBLANK(...), TODAY(), ...).
Basically just substitute today's date if it is blank.
@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])
@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
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |