cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IanCockcroft
Helper V
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.

thanks   in advance

Ian

8 REPLIES 8
amitchandak
Super User
Super User

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

 

For more refer : https://www.andredevelopment.com/en-US/community/blogs/powerbi/working-days-holidays-calendar/

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!
AiolosZhao
Memorable Member
Memorable Member

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

 

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Aiolos,

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

thanks a mil

Ian

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

 

2020-05-17 20_05_33-Untitled - Power BI Desktop.png



Did I answer your question? Mark my post as a solution!
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

This is perfect. exactly what I  neede.

thanks so much

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 I answer your question? Mark my post as a solution!
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

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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()))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!