How to find total work days between two dates while using date slicer

[ajitsahoo8338]

Hello Everyone. I need help with this.

Project_Name	Baseline_StartDate	Baseline_EndDate
E0107	12/8/2024	12/12/2024
E0124	7/25/2023	18/12/2024

This is sample data. I want to calculate the total working days between Baseline_StartDate and Baseline_EndDate for each project, excluding Saturday and Sunday. But the main issue is that when the date slicer is applied I am getting the wrong output. Let's say there is one date slicer in the view, and the date range of that date slicer is 12/9/2024 to 12/13/2024. So the working days for E0107 would be 4, and for E0124, the working days would be 5. How can I achieve this?

Thank You,
Ajit

[Kedar_Pande]

@ajitsahoo8338 

Create a weekend column in caleder table:

IsWeekend = WEEKDAY('DateTable'[Date], 2) > 5

This will return TRUE for Saturdays and Sundays.

Measure:

Total Working Days = 
VAR StartDate = MAX('ProjectTable'[Baseline_StartDate])
VAR EndDate = MIN('ProjectTable'[Baseline_EndDate])
VAR FilteredDates = 
FILTER(
'DateTable',
'DateTable'[Date] >= StartDate &&
'DateTable'[Date] <= EndDate &&
'DateTable'[IsWeekend] = FALSE() &&
'DateTable'[Date] IN ALLSELECTED('DateTable'[Date])
)
RETURN 
COUNTROWS(FilteredDates)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Bibiano_Geraldo]

Hi @ajitsahoo8338 ,

Make sure that you have a calendar table, and paste this DAX to create a new calculated column:

IsWorkingDay = 
IF(
    WEEKDAY('Calendar'[Date], 2) <= 5, -- Weekday function with 2 = Monday as start of the week
    TRUE(),
    FALSE()
)

Now relate the calendar table with your project table using baseline_startDate

Use this measure to count working days:

Total Working Days = 
VAR StartDate = MAX('Project'[Baseline_StartDate]) -- Get the project start date
VAR EndDate = MIN('Project'[Baseline_EndDate]) -- Get the project end date
VAR SelectedDates = 
    CALCULATETABLE(
        'Date',
        'Date'[Date] >= StartDate &&
        'Date'[Date] <= EndDate
    )
RETURN
COUNTROWS(
    FILTER(
        SelectedDates,
        'Date'[IsWorkingDay] = TRUE()
    )
)

Make sure to replace column and table names with your owns

[anmolmalviya05 · Accepted solution]

Hi , Hope you are doing good !

To calculate the total working days between Baseline_StartDate and Baseline_EndDate for each project, excluding weekends, while respecting a slicer's date range, you can follow these steps in Power BI:

Step 1: Create a Date Table

Create a Date Table with columns for dates, weekdays, and a working day flag.

Use Power Query or DAX to generate this table.

Example DAX for a Date Table:

DateTable =

ADDCOLUMNS(

CALENDAR(DATE(2023, 1, 1), DATE(2024, 12, 31)),

"DayOfWeek", WEEKDAY([Date], 2), -- 2 returns Monday as 1

"IsWorkingDay", IF(WEEKDAY([Date], 2) <= 5, 1, 0) -- 1 for Mon-Fri

)

Step 2: Create a Measure for Total Working Days

Use a measure to calculate the total working days:

Total Working Days =

VAR StartDate = MAX('Projects'[Baseline_StartDate])

VAR EndDate = MIN('Projects'[Baseline_EndDate])

VAR FilteredDates =

FILTER(

'DateTable',

'DateTable'[Date] >= StartDate &&

'DateTable'[Date] <= EndDate &&

'DateTable'[IsWorkingDay] = 1

)

RETURN

COUNTROWS(FilteredDates)

Step 3: Respect the Slicer

Ensure the slicer is applied to the DateTable. The measure automatically respects the slicer because it filters the DateTable based on the date range selected in the slicer.

Step 4: Add the Measure to Your Visual

Add the Total Working Days measure and ensure your table or visual includes both the Projects table and the DateTable.

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

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

@ajitsahoo8338

[Tahreem24]

@ajitsahoo8338 Check if the below blog work for you:

https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/Calculate-the-working-days-between...