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.
Hi there,
I want to calculate the difference of two dates in days by excluding weekends in power query. I got a date table which has columns that tells whether the date lies on weekday and weekend. I used the following formula for calculating the difference in days but dont know how to exclude the weekends. Any help would be really appreciated.
Duration.Days([Date1] - [Date2])
Solved! Go to Solution.
Here is one way to do it with a custom column with this formula (Date2 is the earlier date in this case).
= List.Count(List.Select(List.Dates([Date2], Duration.TotalDays([Date1]-[Date2]) + 1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Dunner2020,
Did mahoneypat 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.
If the above doesn't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Here is one way to do it with a custom column with this formula (Date2 is the earlier date in this case).
= List.Count(List.Select(List.Dates([Date2], Duration.TotalDays([Date1]-[Date2]) + 1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
How beatiful is this code
I believe that the List.Contains({1,2,3,4,5} should actually be List.Contains({0,1,2,3,4}, otherwise this works perfectly! Thank you!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |