If date falls within the current month
I am trying to calculate the # of visits (that a therapist does) per working day against a goal of how many visits per day we budgeted them to do in a month. I am running into an issue with the calculation for the current month.
For example, the goal for June was 22 visits a day and we had 733 visits in the month of June and 22 working days in the month. So I have 733 visits/ 22 working days to get me 33.12 visits per day and a variance of 11.32 vs our goal of 22.
But July is currently happening so it does not make sense to divide it by the total number of working days in the month, it needs to be the number of working days passed this month. I have another beast mode to calculate that and it is working to show me how many days we are in to the current month.
What I cant figure out is the proper beast mode for:
case when todays date is apart of the month of date, then divide it by business days into the month
else divide it by total working days in a month
or an alternative would be to get the calculation for the business days into the month to calculate it based on the date column vs todays date (so it would show 22/22 days into June and 15/22 days into July), but I also am unsure how to do that.
Any suggestions?
Answers

Would something like this work:
CASE WHEN DATE_FORMAT(`datefield`,'%m/%Y') = DATE_FORMAT(CURRENT_DATE(),'%m/%Y') THEN partial month logic ELSE full month logic END
Basically, if the date if part of the current month then do a partial month calculation.
If I solved your problem, please select "yes" above
0 
Ohh I had not tried that yet, and sounds like it should work, but I am getting a calculation error on it.
0 
Wouldn't you just want MAX for both of the working days in month denominators?
If I solved your problem, please select "yes" above
0 
The error was the SUM that is in black still but still did not get it right for me. Max is correct though.
0 
@MThebeau I would recommend restructuring your formula so that @colemenwilson's case statement resides within the denominator sum to ensure that all your conditions have the same granularity. Also MagicETL actually has a function in formula editor called DATE_WORKING_DIFF that calculates number of working days between two dates, so I would use that to hardcode the number of working days to date in the current month like this:
DATE_WORKING_DIFF(CURRENT_DATE(),CURRENT_DATE()  DAY(CURRENT_DATE()) + 1)
Then you can reference that new column in the final beast mode like this:
sum(`Total Billable Hours`) / sum( case when LAST_DAY(`Date`) = LAST_DAY(CURRENT_DATE()) then `MTD Working Days` else `Working Days in Month` end )
2 
@MThebeau
You could try this
CASE
WHEN
CONCAT(YEAR('YourDateField'),MONTH('YourDateField')) = CONCAT(YEAR(CURDATE()),MONTH(CURDATE()))
THEN
DAYOFMONTH(CURDATE())  'holidaysforthatmonth'
ELSE
'your working days in the month'
END
Try this logic out  this is just the logic part, the syntax would vary based on your dataset fields.
Let us know if this does not work'Happy to Help'0 
I am trying to do something similar, but I am wanting to use my current month's data/working day to project the total visits for next month. The formula I have below is how im calculating current month:
(SUM(case when
Appointment Status
= 'Arrived' thenVisit Count
end) / (MAX(COUNT(DISTINCT case whenAppointment Status
= 'Arrived' thenBillable Date
end)) over (partition byAppointment Start Date
))) +Visits/BDVariable
0
Categories
 All Categories
 1.4K Product Ideas
 1.4K Ideas Exchange
 1.4K Connect
 1.2K Connectors
 284 Workbench
 4 Cloud Amplifier
 4 Federated
 2.9K Transform
 88 SQL DataFlows
 554 Datasets
 2.2K Magic ETL
 3.3K Visualize
 2.3K Charting
 562 Beast Mode
 9 App Studio
 27 Variables
 577 Automate
 140 Apps
 414 APIs & Domo Developer
 22 Workflows
 1 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 350 Distribute
 90 Domo Everywhere
 258 Scheduled Reports
 2 Software Integrations
 91 Manage
 88 Governance & Security
 9 Product Release Questions
 Community Forums
 42 Getting Started
 28 Community Member Introductions
 85 Community Announcements
 4.8K Archive