Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I'm trying to calculate the number of Non-Weekend days between two dates. Basically what I've done is create a date table 'CountWorkingDays' that contains all dates from 1/1/2016 to Today and a column that numerically indicates the day of the week (Sun-Sat, 1-7).
The calculated column I'm using is called '2to3'
The Date columns I'm comparing are 'DateSentToVRMGroup' and 'DateApprovedProcessed'
The formula I'm trying to use is:
2to3 = if(or(ISBLANK([DateSentToVRMGroup]),ISBLANK([DateApprovedProcessed])),9999999,if([DateSentToVRMGroup]>[DateApprovedProcessed],0,CALCULATE(COUNT(CountWorkingDays[DayofWeek]),FILTER(CountWorkingDays,CountWorkingDays[Date]>[DateSentToVRMGroup] && CountWorkingDays[Date]<[DateApprovedProcessed] && CountWorkingDays[DayofWeek]<>1 && CountWorkingDays[DayofWeek]<>7))))
The formula isn't generating an error but all of the rows are coming up blank. Is there something I'm doing wrong?
Thanks in advance for the help!
Solved! Go to Solution.
You could try adding the following calculated column. Just replace 'Table2' with your table name.
No date table requried for this one 🙂
Count of Weekdays = VAR StartDate = DATE(2016,1,1) VAR EndDate = TODAY() VAR WeekDayTable = SELECTCOLUMNS(FILTER(ADDCOLUMNS(CALENDAR(StartDate,EndDate),"WeekDay",IF(WEEKDAY([Date])in {1,7},0,1)),[WeekDay]=1),"Week Day",[Date]) VAR Result = COUNTROWS(CALCULATETABLE(filter(CROSSJOIN(WeekDayTable,Table2),'Table2'[DateSentToVRMGroup]<[Week Day] && [DateApprovedProcessed]>[Week Day]))) RETURN Result
You could try adding the following calculated column. Just replace 'Table2' with your table name.
No date table requried for this one 🙂
Count of Weekdays = VAR StartDate = DATE(2016,1,1) VAR EndDate = TODAY() VAR WeekDayTable = SELECTCOLUMNS(FILTER(ADDCOLUMNS(CALENDAR(StartDate,EndDate),"WeekDay",IF(WEEKDAY([Date])in {1,7},0,1)),[WeekDay]=1),"Week Day",[Date]) VAR Result = COUNTROWS(CALCULATETABLE(filter(CROSSJOIN(WeekDayTable,Table2),'Table2'[DateSentToVRMGroup]<[Week Day] && [DateApprovedProcessed]>[Week Day]))) RETURN Result
Thanks, it worked great!!!
User | Count |
---|---|
94 | |
79 | |
74 | |
63 | |
59 |
User | Count |
---|---|
110 | |
101 | |
84 | |
64 | |
62 |