Forum Discussion
How to find total work days between two dates while using date slicer
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
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
4 Replies
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
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
Create a weekend column in caleder table:
IsWeekend = WEEKDAY('DateTable'[Date], 2) > 5This 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
