Forum Discussion
Working days in current month
I am looking for a formula to calculate the number of working days in the current month. My source is a database, so I am unable to create a calendar table or use the NETWORKDAY function unfortunately.
Is there another way I can write this in a formula instead?
Thank you
Since you cannot use CALENDAR or GENERATESERIES, you can take a different approach by iterating over a range of days using a loop-like function. Try below solution
WorkingDays_CurrentMonth =
VAR StartDate = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR EndDate = EOMONTH(StartDate, 0)
VAR DaysCount =
SUMX(
ADDCOLUMNS(
SELECTCOLUMNS(UNION(SELECTCOLUMNS({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31},
"Offset", [Value])
),
"Date", StartDate + [Offset] - 1,
"Weekday", WEEKDAY(StartDate + [Offset] - 1, 2)
),
IF([Date] <= EndDate && [Weekday] < 6, 1, 0)
)
RETURN DaysCount🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
5 Replies
- grazitti_sapna
Super User
Hi EmWy24,
Certainly Yes, you can do it in DAX dynamically, use below DAX:
WorkingDays_CurrentMonth =
VAR StartDate = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR EndDate = EOMONTH(StartDate, 0)
VAR AllDays = ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"Weekday", WEEKDAY([Date], 2) -- change as per your requirements (2 means Monday = 1, Sunday = 7)
)
RETURN COUNTROWS(FILTER(AllDays, [Weekday] < 6)) -- change as per your requirements Exclude Saturdays (6) and Sundays (7)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together! Thank you for trying to help. Unfortunately CALENDAR is not a function for me, so this formula only errors.
Is there another way?
- grazitti_sapna
Hi EmWy24,
You can try this
WorkingDays_CurrentMonth =
VAR StartDate = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR EndDate = EOMONTH(StartDate, 0)
VAR AllDays =
ADDCOLUMNS(
GENERATESERIES(StartDate, EndDate, 1),
"Weekday", WEEKDAY([Value], 2) -- 2 means Monday = 1, Sunday = 7
)
RETURN
COUNTROWS(FILTER(AllDays, [Weekday] < 6)) -- Excludes Saturdays (6) & Sundays (7)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Sorry, but GENERATESERIES is not available to me
