Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IanCockcroft
Post Patron
Post Patron

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/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.