Forum Discussion
Column that Calculates Business Days
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,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,
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,
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,
That did it - thanks again!!!!