Business Days in the month

Hey @user04816, here's a statement that works in a MySQL dataflow that can pull all weekdays for a current month for a specific date. (does not account holidays). 

(DATEDIFF(LAST_DAY(`Date`),`Date`) + 1) -
((WEEK(LAST_DAY(`Date`)) - WEEK(`Date`)) * 2) -
(case when weekday(`Date`) = 6 then 1 else 0 end) -
(case when weekday(LAST_DAY(`Date`)) = 5 then 1 else 0 end)

You could then divide in a count(distinct(`date`)) where dayofweek(`date`) between 2 and 6 and `date` <= current_date(). 

This would theoretically get you a percentage of working days in the month used. (Not accounting for holidays). Let me know if this is helpful!

I'd use a Progress Bar card for this use case.

To accomplish this, you'd need to create three beast mode calculations - one for the total number of business days in the current month (to use as the max value) ,  second for the number of business days into the month we currently are, and a third that is just a simple title for the progress bar. The code will look a little scary because working with dates can be messy business, but the beast modes would look like this:

For Total Business Days in Month:

MAX(
 DAY(LAST_DAY(CURRENT_DATE())) - (FLOOR(DAY(LAST_DAY(CURRENT_DATE())) / 7) * 2)
-
CASE 
 WHEN Mod(DAY(LAST_DAY(CURRENT_DATE())), 7) = ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) OR ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) = 1 THEN 1
 WHEN Mod(DAY(LAST_DAY(CURRENT_DATE())), 7) > ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) THEN 2
 ELSE 0
END
 )

For Business Days into the Month:

MAX(
 DAY(CURRENT_DATE()) - (FLOOR(DAY(CURRENT_DATE()) / 7) * 2)
 -
 CASE 
 WHEN Mod(DAY(CURRENT_DATE()), 7) = ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) OR ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), '%Y-%m-%d')) ) = 1 THEN 1
 WHEN Mod(DAY(CURRENT_DATE()), 7) > ((7 - ( DAYOFWEEK(STR_TO_DATE(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), '%Y-%m-%d')) ) ) + 1) THEN 2
 ELSE 0
 END
)

For Title (can be any text you like):

'How far along are we in the month?'

It's the same formula in both of the first two, just with a LAST_DAY function added to the Total business days version in order to do the calculation for the last day of the month.

Once you've built these beast mode calculations, just add them to the Progress Bar card with Title as GAUGE NAME, Business Days into the Month as GAUGE VALUE, and Total Business Days in Month as MAX VALUE.

I hope this helps!

Total days is much, much simpler.

For Total Days in the month:

MAX(DAY(LAST_DAY(CURRENT_DATE()))) 

For Days into the month:

MAX(DAY(CURRENT_DATE()))

@zcameron I'm trying to work through your total business days in the month calculation but I'm having some difficulty figuring out how I can include Saturdays in this calculation. Is this possible?

@zcameron is it also possible to get this same formula but for days in the current week, quarter and year?

@zcameron that worked perfectly, thank you! Now I just need to figure out how to do this for current quarter and current year.

@lb1234 - Unfortunately, I've not created this type of calculation for other time frames.

Calculating the start and end days for the quarter version could be pretty complex. I would definitely look at an ETL/join-based solution for that.

For the year calculation, you know the total will either be 313 or 312 because there will always be either 52 weeks in a year or, if Jan 1 is a Sunday, 53, or if it is a leap year and Jan 1 or Jan 2 is a Sunday, then 53.

Your numerator would be that number minus how many Sundays have passed (number of days into the year [ DAYOFYEAR(CURRENT_DATE() ] divided by 7). Probably some more details in there with the starting day of the year to work out as well... Also complex enough that it might be best to look at an ETL solution.