My data consists of fully_enrolled_at as dimension and new_users as measure. Therefore, this doesn't require a dataset, since any date and measure you can sum() will do.
I created the following 2 calculations:
#this is number of calendar days in the month
days_in_month = DATEDIFF('day',MIN(DATETRUNC('month',[fully_enrolled_at])),MAX(DATETRUNC('month',DATEADD('month',1,[fully_enrolled_at]))))
#this changes number of calendar days in the current month to the number of days elapsed:
days_in_all_months = IF MIN(DATETRUNC('month',today()))=MAX(DATETRUNC('month',[fully_enrolled_at])) THEN 1+DATEDIFF('day',DATETRUNC('month',today()),today()) ELSE [days_in_month] END
Then I plotted: discrete month(fully_enrolled_at) as dimension, and sum([new_users])/[days_in_all_months] as measure.
This gives me what I am looking for, however I would like to account for business days in each of the months, not calendar month.
How do I remove weekends and national holidays from days_in_all_months (while current month should have # of business days elapsed)?
Can you assist with adjusting this? Thank you!