Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
I am trying to find a way to calculate a date that is 22 working days past a date that is entered into a table.
Need to make sure that weekends and federal holidays are not part of that 22 day count.
I tried using NETWORKDAYS, but that option is not available in the environment I am working in.
Example;
Date entered 12/31/22 + 22 working days = 2/2/23.
I need to calculate with a formula that will produce results for every day entered
Solved! Go to Solution.
Hi @Anonymous
I make an example on Calendar table:
1.Create a Calendar table, it includes weekday column:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2023,12,31)),
"Weenday",WEEKDAY([Date],2))
2.Create a holiday table to input the holiday and related date
3.Then create the inputdate measure and the outputdate measure:
Outputdate = MAXX(TOPN(23,FILTER(ALL('Calendar'),[Date]>[Input_date]&&[Weenday]<6&&[Date]<>MAX(Holiday[Date])),[Date],ASC),[Date])
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
I make an example on Calendar table:
1.Create a Calendar table, it includes weekday column:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2023,12,31)),
"Weenday",WEEKDAY([Date],2))
2.Create a holiday table to input the holiday and related date
3.Then create the inputdate measure and the outputdate measure:
Outputdate = MAXX(TOPN(23,FILTER(ALL('Calendar'),[Date]>[Input_date]&&[Weenday]<6&&[Date]<>MAX(Holiday[Date])),[Date],ASC),[Date])
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
And how do I get it to work for the next 10 years? Do I have to build a table that has all holidays for the next 10?
Hi @Anonymous
Based on the solution in offered, you need to create a table to store the holiday.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I need to calculate a date that is 45 work days past the start date. I have to account for all weekends and holidays, then give users a deadline for them to be finished with the task which is 45 work days after it was entered in the system
@Anonymous You could try the old school method: Net Work Days - Microsoft Power BI Community
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
14 | |
12 | |
9 |