If date falls within the current month

Options
MThebeau
MThebeau Member
in Beast Mode

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?

Screenshot 2023-07-21 143137.png
Screenshot 2023-07-21 143122.png

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    You can use the LAST_DAY function to determine if the current day is part of the same month:

    CASE WHEN LAST_DAY(CURRENT_DATE()) = LAST_DAY(`Date`) THEN
  [Current month number of working days]
ELSE
  [total month working days]
END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Aditya_Jain
    Aditya_Jain Contributor
    Answer ✓
    Options

    Hi @MThebeau
    You may use the Domo Dimension.
    Go to connectors and type dimension, It should look like the following screenshot

    image.png

    'Happy to Help'

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    You can use the LAST_DAY function to determine if the current day is part of the same month:

    CASE WHEN LAST_DAY(CURRENT_DATE()) = LAST_DAY(`Date`) THEN
  [Current month number of working days]
ELSE
  [total month working days]
END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MThebeau
    MThebeau Member
    Options

    Thank you!! @GrantSmith Do you know about a calendar that DOMO has built out that can be shared that has the working days for the year built out to include holidays since the SQL code for it does not?

  • Aditya_Jain
    Aditya_Jain Contributor
    Answer ✓
    Options

    Hi @MThebeau
    You may use the Domo Dimension.
    Go to connectors and type dimension, It should look like the following screenshot

    image.png

    'Happy to Help'

Categories