Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 ...
General Comment
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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
    )

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

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
)