Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with a field called "Orderdate" and I would like to X number of working days to this date to give a new date the forecasted delivery date.
Currently I just add a number of days see below
Due Date 14 = BeauPark_Backorder_Analysis_PowerBI_Export[Order Date]+14
However I want to add working days only please note I do not have calendar table
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:
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.
Hi and many thanks for your reply however i get the following when i copy this into my model and I am not sure how to resolve.
Jim
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
98 | |
79 | |
74 | |
66 |
User | Count |
---|---|
136 | |
109 | |
104 | |
82 | |
73 |