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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jb123
Frequent Visitor

Column that Calculates Business Days

Hi,

 

I need to create a column that calculates the business days. I have this working that displays the calendar days in a column and I just need to tweek it to exclude weekends:

 

Days =
SWITCH (
    TRUE (),
    'Velocity'[DashboardCreationDate] <Velocity[Completed Date] , DATEDIFF ('Velocity'[DashboardCreationDate], 'Velocity'[Completed Date], DAY ),
    'Velocity'[DashboardCreationDate] > 'Velocity'[Completed Date], DATEDIFF ('Velocity'[Completed Date], 'Velocity'[DashboardCreationDate], DAY ) * -1,
    0
)

 

TIA!!!

2 ACCEPTED SOLUTIONS

@jb123,

Use the following DAX to create date table and check if you get any errors.

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )



Regards,

Community Support Team _ Lydia Zhang
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

@jb123,

Use the following DAX instead.

Days excluding Weekends = IF(ISBLANK(Velocity[Completed Date])||ISBLANK(Velocity[DashboardCreationDate]),BLANK(),SWITCH (
    TRUE (),
'Velocity'[DashboardCreationDate] <Velocity[Completed Date] , CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],Velocity[DashboardCreationDate],Velocity[Completed Date] )),
    'Velocity'[DashboardCreationDate] > 'Velocity'[Completed Date],CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],Velocity[Completed Date],Velocity[DashboardCreationDate] )) * -1,
    0
))



Regards,

Community Support Team _ Lydia Zhang
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

9 REPLIES 9
v-yuezhe-msft
Employee
Employee

@jb123,

Firstly, create a date table in Power BI Desktop following the instructions in this blog.

Secondly, create a new column in the date table using the DAX below.

IsWorkDay = SWITCH(WEEKDAY('Date'[Date]),1,0,7,0,1)

Thirdly, create a new column using the following formula in  your original table.

Days excluding Weekends = SWITCH (
    TRUE (),
    'Velocity'[DashboardCreationDate] <Velocity[Completed Date] , CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],Velocity[DashboardCreationDate],Velocity[Completed Date] )),
    'Velocity'[DashboardCreationDate] > 'Velocity'[Completed Date],CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],Velocity[Completed Date],Velocity[DashboardCreationDate] )) * -1,
    0
)


1.PNG


Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The syntax to create a date table in the blog is incorrect.  If you click on new table from moedling and do a copy and paste of the following:

 

Date =CALENDAR (DATE(2000;1;1); DATE(2025;12;31))

 

You get an error saying the syntax is incorrect so i thought ok I should copy and paste all of it so I did a copy and paste of this instead

 

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

 

Again I get a syntax error.  The exact error message is "The syntax for ';' is incorrect. (DAX(CALENDAR (DATE(2000;1;1); DATE(2025;12;31))))."

 

 

Any suggestions?

@jb123,

Use the following DAX to create date table and check if you get any errors.

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )



Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

One last issue.  In some situations one of the date columns is null.  Is there a way to modify this:

 

Days excluding Weekends = SWITCH (
    TRUE (),
    'Velocity'[DashboardCreationDate] <Velocity[Completed Date] , CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],Velocity[DashboardCreationDate],Velocity[Completed Date] )),
    'Velocity'[DashboardCreationDate] > 'Velocity'[Completed Date],CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],Velocity[Completed Date],Velocity[DashboardCreationDate] )) * -1,
    0
)

so that it just puts a null value if one of the values in either column is null?  Right now it lists an odd number for the result in these situations..

 

TIA

 

@jb123,

Use the following DAX instead.

Days excluding Weekends = IF(ISBLANK(Velocity[Completed Date])||ISBLANK(Velocity[DashboardCreationDate]),BLANK(),SWITCH (
    TRUE (),
'Velocity'[DashboardCreationDate] <Velocity[Completed Date] , CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],Velocity[DashboardCreationDate],Velocity[Completed Date] )),
    'Velocity'[DashboardCreationDate] > 'Velocity'[Completed Date],CALCULATE(SUM('Date'[IsWorkDay]),DATESBETWEEN('Date'[Date],Velocity[Completed Date],Velocity[DashboardCreationDate] )) * -1,
    0
))



Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That did it - thanks again!!!!

You rock!  That works great!!!!!!!!!!!

Thanks I will give this a try and see if I can get it to work.

MFelix
Super User
Super User

Hi @jb123,

From your formula I cannot perceive where you want to take out the bussines days but if you use the WEEKDAY formula you can then exclude the weekday = 1 (Sunday) or 7 (saturday).

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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