Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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/
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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDo 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
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe 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()))
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |