Forum Discussion
Calculate working days between two dates
Hi everyone,
I have two date columns in Power BI (Site Version Date, Country Version Date). How should I calculate the number of working days between these two columns? Thank you!
I also set up a calendar table but I'm not sure if this table is neccessary.
Anonymous , First create a date table using below DAX go to modelling click on new table
DateTable =
ADDCOLUMNS (
CALENDAR (MIN('YourTable'[Site Version Date]), MAX('YourTable'[Country Version Date])),
"IsWorkingDay",
IF (
WEEKDAY ( [Date], 2 ) < 6,
TRUE,
FALSE
)
)Mark this table as date table
Then create a calculated column in your main table using
WorkingDaysBetween =
VAR StartDate = 'YourTable'[Site Version Date]
VAR EndDate = 'YourTable'[Country Version Date]
RETURN
CALCULATE (
COUNTROWS ( DateTable ),
DateTable[Date] >= StartDate,
DateTable[Date] <= EndDate,
DateTable[IsWorkingDay] = TRUE
)
2 Replies
Anonymous , First create a date table using below DAX go to modelling click on new table
DateTable =
ADDCOLUMNS (
CALENDAR (MIN('YourTable'[Site Version Date]), MAX('YourTable'[Country Version Date])),
"IsWorkingDay",
IF (
WEEKDAY ( [Date], 2 ) < 6,
TRUE,
FALSE
)
)Mark this table as date table
Then create a calculated column in your main table using
WorkingDaysBetween =
VAR StartDate = 'YourTable'[Site Version Date]
VAR EndDate = 'YourTable'[Country Version Date]
RETURN
CALCULATE (
COUNTROWS ( DateTable ),
DateTable[Date] >= StartDate,
DateTable[Date] <= EndDate,
DateTable[IsWorkingDay] = TRUE
)
