To add a specific number of working days (business days) to a date in Power BI without using a calendar table, you can create a custom measure using DAX. You can use a loop in the DAX formula to iterate through the days and skip weekends (Saturday and Sunday) while adding working days. Here's an example of how you can do it:
Due Date Working Days =
VAR StartDate = BeauPark_Backorder_Analysis_PowerBI_Export[Order Date]
VAR WorkingDaysToAdd = 14 // Change this to the number of working days you want to add
VAR CurrentDate = StartDate
VAR DaysToAdd = 0
WHILE (DaysToAdd < WorkingDaysToAdd)
IF (WEEKDAY(CurrentDate, 2) <> 6 && WEEKDAY(CurrentDate, 2) <> 7)
// Check if CurrentDate is not Saturday (6) or Sunday (7)
SET CurrentDate = CurrentDate + 1
SET DaysToAdd = DaysToAdd + 1
ELSE
SET CurrentDate = CurrentDate + 1 // Move to the next day
END IF
END WHILE
RETURN CurrentDate
In this DAX measure:
- StartDate is the initial order date.
- WorkingDaysToAdd is the number of working days you want to add (in your example, it's set to 14).
- CurrentDate is a variable used to keep track of the current date being evaluated.
- DaysToAdd is a variable used to count the working days added.
The WHILE loop iterates through each day, checking if it's a Saturday (6) or Sunday (7) and only adds to DaysToAdd if it's a working day (not Saturday or Sunday). It continues until DaysToAdd reaches the desired number of working days.
You can then use this "Due Date Working Days" measure in your visuals to calculate the forecasted delivery date based on working days.
