Forum Discussion

EmWy24's avatar
EmWy24
Regular Visitor
1 year ago
Solved

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 unfortunate...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    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!

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
1 year ago

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!

EmWy24's avatar
EmWy24
Regular Visitor
1 year ago

Sorry, but GENERATESERIES is not available to me

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    1 year ago

    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!