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.........

[SauronOfMordor]

Go to bed. Sleep it off.

[FourtySeven]

I think I will thank you. I have a nice tasty adult beverage to aid in such an endeavor.

[rfp1234]

[SuperLuminal]

I remember Dax when she was still a virgin...

[WinMod70]

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.

[Larry Lucido]

How to count working days with GEORGE.

[zeestephen]

What is DAX?

[Robert DeLong]

Data Analysis Expressions

[SauronOfMordor]

I assume this is the DAX software you are referring to

https://en.m.wikipedia.org/wiki/DAX_(application)

[lefty-lie-spy]

FFS, what are you talking about?
Fix your title to something that makes sense.

[drSteve78]

FFS, what are you talking about?

---

Echo that, The Universe has a way to deal with people who post what many would describe as Incoherent Jargon.

As the OP is now discovering. Enjoy your confusion. Hope your find your Disgronifier in time.

[bhl]

This is not stackoverflow.com. But if you had looked there, you would have found a half dozen solutions in various programming languages.. Hope you didn’t spend 2 weeks trying to avoid doing a 31 element loop. That’ll take some CPU time to recoup. :)

[Macoozie]

Lucky for us, we dodged that bullet.
(for a while . . .)

For those that remember formatting blue screen WordPerfect . . .
Then crazy formulas in Excel
Then TSQL and leaps and falls trying .NET structures cuz “ . . . it’s gonna solve everything!”.
Then SSRS
Then we leaped, back to the future, in re-birthed “R”.

Gonna get back to DAX or similar MS lockdown junk for the PowerBi crapola folks seem to want today.

The strangle hold systems and tools get on people can be a tough thing to break free from. Sometimes, teach ‘em a lesson and just give ‘em what they ask for and watch the heads roll.

If it’s such a big, big world, why does the same shiite keep floating back up to the top?.

[drSteve78]

Analog guy here with a simple solution. Get a calendar and a pencil. Start numbering work days each month. If you choose, you can then add them up to find workdays in a year. For your own personal work days available, If you have two weeks vacation, 50 weeks times M-F 5 is 250 days max, minus taken holidays.
Since i am in an ambulatory animal health care profession as a solo Lone Ranger, that number of work days is general 365, except for Leap year.
I am trying really really hard not to be FRivolous and sarcastic.

[dirtymac]

You were in 3rd grade with her

[higgmeister]

Good Heavens!   Just what the world needed another specialized programming script! /s

https://en.wikipedia.org/wiki/List_of_programming_languages_by_type#Scripting_languages

[SuperLuminal]

"You were in 3rd grade with her"

I remember it well... That's the year the japs bombed Pearl Harbor...

[ProtectOurFreedom]

STAX, not DAX...

[FourtySeven]

Hope you didn’t spend 2 weeks trying to avoid doing a 31 element loop. That’ll take some CPU time to recoup. :)

I did! (in a way) haha...it's ok. Lesson learned: work on a personal computer (or VM) one has admin rights to! Yes yes...

(side note: stackoverflow.com is blocked on my work computer...dang firewall rules....grumble...). Thanks for the friendly flame!