To Count Working Days in any Month with DAX

Now | Me

[FourtySeven]

haha ok, really?!? No one has bothered to post this for struggling newbies in DAX like me? In the words of our bumbling leader "C'mon Man!"

Yeah ok, I get that DAX whizes probably figured this out on their first day but whatever.

Thought I'd post this here, so maybe it'll show up on some search somewhere, and help a poor schlep like me out sometime, working through this for the last few WEEKS, going nowhere with this. (this is why I haven't posted anything about election irregularities by the way in recent weeks, been struggling with this dang WORK problem, now I gotta go back and code everything differently because I worked around it anyway...)

Not to mention most DAX "help" is posted in the syntax of German users using the ";" in place of the "," thanks ya dang Krauts.

Anyway assuming one has a (pretty much) standard date table with something like below (the critical column is the [DayofWeekNumber]):

Date = ADDCOLUMNS(CALENDAR(DATE(2015,1,1), DATE(2050,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "YearMonthNumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "IsWorkingDay", NOT WEEKDAY( [Date] ) IN {1,7}, "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )

Then use this dang DAX for a calculated column:

Working Day Count = SWITCH ('Date'[DayOfWeekNumber],7,0,6,1,5,1,4,1,3,1,2,1,1,0)

THAT is what took me literally the last two weeks to figure out ......sheesh....

I had to post this somewhere. Flame away for my stupidity yes, those who are DAX gods. Actually I wouldn't mind a bit of flaming for this, because really I should've figured it out sooner, but yeah, stuck for two weeks, coded around it, now I gotta go BACK and change alllllll these other column names blah blah blah.....so yeah won't be around for a while again.

In all seriousness, hope it helps someone. Lots more work now so again, another FR/Internet Fun wherever hiatus.........

[FourtySeven]

</like> I like jazz.......RETURN
“I like jazz”....
Or something like that.

[FourtySeven]

Thank you.

The problem is, I don’t have a pencil.

(or a calendar)

[FourtySeven]

I don’t see DAX listed there.

Must be an oversight.

</oversight>

SUMMARY(’Oversight’[Oversight1], ‘Return table[Date],[Date],’Order Summary[Order Date], [Date].[Date], 0,True)

[FourtySeven]

Don’t forget M!

[FourtySeven]

Was I not supposed to post this thread?

Was that bad?

</costanza>

[FourtySeven]

I’m guessing that you’re one of the laid off pipeline workers that jumped right into the programmer field. Looks like the transition is going well.

Yes! I figger I should have the oxetane line linked to the CO2 tank in no time...............

(did I fake it enough there? I wonder if Crazy Uncle Joe would hire me)

[FourtySeven]

Yes! Close () of course.

[FourtySeven]

*sigh* Terry Farrell

</dreamy music>

[SamAdams76]

Now if you used Excel, you could use a much simpler formula such as this:

=NETWORKDAYS (start_date, end_date, [holidays])

The holidays part is optional.

[FourtySeven]

*SiGH*

Tell me about it!

I mean that’s ALL a brotha is asking for in DAX ... who thought it was a good idea to remove that function?!

Oh yeah....DAX is all about columns.....yah yeah whatever...unghh