Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |