cancel
Showing results for
Did you mean:
Helper V

Current Working Day of Month

Hi guys and gals,

how do i calculate the currrent working day from the current date  using DAX?

This is  for a label on a dash board that      tells  the user what day they on.

I    have a work aroung, but its not very elogant.

Ian

8 REPLIES 8
Super User

WOrk Day = if( WEEKNUM([Date],2) <6,1,0) // Week day and week end

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Memorable Member

what do you mean the current working day? could you please give some sample that you want to get?

Aiolos Zhao

Proud to be a Super User!

Helper V

Hi Aiolos,

I mean what working day of month is TODAY()?

thanks a mil

Ian

Super User

This is so wrong, but I did this with a measure that doesn't use a date table. You should always use a date table...

``````WorkDayCount =
VAR UsedDate = MAX(Testing[Date])
VAR FirstDayOfMonth =
DATE( YEAR( UsedDate ), MONTH( UsedDate ), 1 )
VAR WorkDays =
COUNTROWS(
FILTER(
CALENDAR(
FirstDayOfMonth,
UsedDate
),
"WeekDayNumber", WEEKDAY(
[Date],
2
)
),
[WeekDayNumber] < 6
)
)

RETURN
WorkDays``````

It returns the workday count for the month of the day in the Date column. You can change the first row of this measure to say

``var UsedDate = TODAY()``

if you always want it to return the working days of the current month through today.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper V

This is perfect. exactly what I  neede.

thanks so much

Super User

Glad I could help @IanCockcroft. As indicated the "right" way to do this is with a true date table. A date table is a very powerful thing and required for date intelligence functions. The measure to calculate workdays would have been about 3-4 lines with a date table. But glad this has you moving forward in your project.

Creating a Dynamic Date Table in Power Query

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Microsoft

Do you mean you just want to show the day # for today?  If you, you can just use

DayToday = Day(Today())

Or if you want the Day of the Week, you can use

DayToday = Format(TODAY(), "DDDD")

You can then just display either in a card visual.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Super User

@IanCockcroft , hope you have month day in your calendar this will give workday for all dates

new columns

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Day This month = if('Date'[Work Day]=1,sumx(filter('Date',[Month Year]=EARLIER('Date'[Month Year]) && [Date]<=EARLIER('Date'[Date]) ),[Work Day]),BLANK())

Or a measure like this

sumx(sum('Date'[Work Day]), filter(all('Date'),format('Date'[Date],"YYYYMMM") =format(Today(),"YYYYMMM") && 'Date'[Date] <=Today()))

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Announcements

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!