Forum Discussion
Working Days Left in QTR
I have a measure for Days left in QTR:
DaysLeftInQTR = DATEDIFF ( TODAY (), EOMONTH ( TODAY (), ROUNDUP ( MONTH ( TODAY () ) / 3, 0 ) * 3 - MONTH ( TODAY () ) ), DAY )
How would I change this so that it only counts week days?
Thank you!
Hi Anonymous
It would be easier to use a Calendar table as support but try this based on what you had:
DaysLeftInQTR = VAR _Today = TODAY() VAR _LastDay = EOMONTH ( _Today, ROUNDUP ( MONTH ( _Today ) / 3, 0 ) * 3 - MONTH ( _Today ) ) VAR _TotalDays = DATEDIFF ( _Today, _LastDay, DAY ) VAR _WeeksInBetween = WEEKNUM ( _LastDay, 2 ) - WEEKNUM ( _Today, 2 ) - 1 VAR _WEndDaysFirstWeek = MIN ( 2, 7 - WEEKDAY ( _Today, 2 ) ) VAR _WEndDaysLastWeek = MAX ( 0, WEEKDAY ( _LastDay, 2 ) - 5 ) VAR _WeekEndDays = (2 * _WeeksInBetween) + _WEndDaysFirstWeek + _WEndDaysLastWeek RETURN _TotalDays - _WeekEndDays
2 Replies
Hi Anonymous
It would be easier to use a Calendar table as support but try this based on what you had:
DaysLeftInQTR = VAR _Today = TODAY() VAR _LastDay = EOMONTH ( _Today, ROUNDUP ( MONTH ( _Today ) / 3, 0 ) * 3 - MONTH ( _Today ) ) VAR _TotalDays = DATEDIFF ( _Today, _LastDay, DAY ) VAR _WeeksInBetween = WEEKNUM ( _LastDay, 2 ) - WEEKNUM ( _Today, 2 ) - 1 VAR _WEndDaysFirstWeek = MIN ( 2, 7 - WEEKDAY ( _Today, 2 ) ) VAR _WEndDaysLastWeek = MAX ( 0, WEEKDAY ( _LastDay, 2 ) - 5 ) VAR _WeekEndDays = (2 * _WeeksInBetween) + _WEndDaysFirstWeek + _WEndDaysLastWeek RETURN _TotalDays - _WeekEndDays