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'm trying to find how to create a weekday count for dates in a start date and end date column. I have used Dattedif for the total days between the dates, however I only want the working days, Monday to Friday. Please can you help me. Many thanks.
Solved! Go to Solution.
Hi @Blondyvl ,
I have created dummy data for test:
In this case, we need to firstly create a new Calendar table:
Calendar = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))
Then create a measure to calculate the count of working days:
Working Days =
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
'Calendar',
[Date] >= MAX ( 'Table'[Start Date] )
&& [Date] <= MAX ( 'Table'[End Date] )
&& WEEKDAY ( [Date], 2 ) IN { 1, 2, 3, 4, 5 }
)
)
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Blondyvl ,
I have created dummy data for test:
In this case, we need to firstly create a new Calendar table:
Calendar = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))
Then create a measure to calculate the count of working days:
Working Days =
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
'Calendar',
[Date] >= MAX ( 'Table'[Start Date] )
&& [Date] <= MAX ( 'Table'[End Date] )
&& WEEKDAY ( [Date], 2 ) IN { 1, 2, 3, 4, 5 }
)
)
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Blondyvl ,
Here a similar thread. You can find various other links in there that I am sure will be helping you!
Working days between two dates - simply! - Microsoft Power BI Community
And here a video with a function in Power Query:
PowerBI: Calculate business/working days between two dates in a new column - YouTube
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |