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
Anonymous
Not applicable

Counting workdays...

I have the master calendar all set with holidays flagged.  I created WorkDay =1 or 0 to indicate if its a workday  or not (sat, sun and holidays = 0). 

 

CALCULATE(SUM(mastercalendar[WorkDay]),DATESBETWEEN(mastercalendar[full_date],requests[date1],requests[date2]))  returns 1, 0 or blank.
 
I want to sum up the workdays between date1 and date2.
 
Not sure what's going wrong.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Crossing my fingers.  Seems to work (the missing piece of the equation)

 

WorkdaysBetween =
CALCULATE(
SUM(mastercalendar[IsWorkday]),
ALL(mastercalendar),
DATESBETWEEN(
mastercalendar[full_date],
requests[requestdate],
requests[completedate]
)
)
 
In case you're interested, in power query
 
Table.AddColumn(#"DayOfWeek", each Date.DayOfWeek([full_date]))
Table.AddColumn(#"IsWeekday", each if [DayOfWeek]=0 or [DayOfWeek]=6 then 0 else 1)
Table.AddColumn(#"IsWeekend", each 1- [IsWeekday])
Table.AddColumn(#"IsHoliday", each if [Holiday]="" then 0 else 1)
##holidays were built using Date patterns
Table.AddColumn(#"IsWorkday", each if [IsWeekend]+[IsHoliday]>0 then 0 else 1)

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

So we found the issue.  Apparently relationships are active in a calculation so it first filtered the calendar for one date and took the IsWorkday which is only 0 or 1.  So now the question becomes - how can I suppress the relationship for this column ?

1. Find a way to do this in power query

2. Find a way to do this in DAX.

 

Scouring the community for a solution

Anonymous
Not applicable

Crossing my fingers.  Seems to work (the missing piece of the equation)

 

WorkdaysBetween =
CALCULATE(
SUM(mastercalendar[IsWorkday]),
ALL(mastercalendar),
DATESBETWEEN(
mastercalendar[full_date],
requests[requestdate],
requests[completedate]
)
)
 
In case you're interested, in power query
 
Table.AddColumn(#"DayOfWeek", each Date.DayOfWeek([full_date]))
Table.AddColumn(#"IsWeekday", each if [DayOfWeek]=0 or [DayOfWeek]=6 then 0 else 1)
Table.AddColumn(#"IsWeekend", each 1- [IsWeekday])
Table.AddColumn(#"IsHoliday", each if [Holiday]="" then 0 else 1)
##holidays were built using Date patterns
Table.AddColumn(#"IsWorkday", each if [IsWeekend]+[IsHoliday]>0 then 0 else 1)
Ashish_Mathur
Super User
Super User

Hi,

I do not see a mistake there.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

I created to columns in One of my Calender

WeekDay = WEEKDAY('Compare Date'[Compare Date])
WeekDay = WEEKDAY('Compare Date'[Compare Date])

And I able to sum same within this calendar

Also There is another Calendar not join to this one, Able to do with that to

Weekdays Second Cal = ( VAR _Cuur_start = Min('Date'[Date Filer]) VAR _Curr_END = Max('Date'[Date Filer]) return calculate(sum('Compare Date'[Working Day]),'Compare Date'[Compare Date] >= _Cuur_start && 'Compare Date'[Compare Date] <= _Curr_END ) )

 

Screenshot 2019-08-24 08.37.47.pngScreenshot 2019-08-24 08.38.08.pngScreenshot 2019-08-24 08.38.21.pngScreenshot 2019-08-24 08.38.36.png

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.