## Selling Days Remaining in Month

czmudzinski Green Belt

## Selling Days Remaining in Month

I need to write a Beast Mode where I take the maximum number of days in the month and subtract days passed, not including today.

To accomplish this, I have:

`DATEDIFF(MAX(`AppointmentDate`),NOW() - 1)`

The challenge is that I want to count only selling days only.

Monday - Friday = 1 selling day

Saturday = 0.5 selling day

Sundday = 0

Any thoughts on how to accomplish this?

Accepted Solutions
czmudzinski Green Belt

## Re: Selling Days Remaining in Month

I was able to solve this using the following beast mode:

```SUM(
CASE
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) >= '2016-12-16' AND DATE(`Date`) <= '2016-12-31' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-11-24' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-09-05' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-07-04' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-05-30' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 1 THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 2 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 3 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 4 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 5 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 6 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 7 THEN 0.5
END)```

All Replies
Shevy Purple Belt

## Re: Selling Days Remaining in Month

CZ, I have done several business day calcs, but to do it, I built a date calendar table that labels days as a business day or not. Currently, DOMO does not have a business date function. So my table has items like: Current Date Business Date (0 or 1) Business day of month (1 to 20 - or 19 or 23 depending on number of bus days in month) Business day of quarter Business day of year You get the idea.
Dojo Community Member
** Please like responses by clicking on the thumbs up
czmudzinski Green Belt

## Re: Selling Days Remaining in Month

I'm trying to accomplish two things:

Revenue Goal for current month / Selling Days in current month, which I have successfully done

and

Revenue Goal for current month / Remaining selling days in month, which I'm still struggling with. I have added a DAYNAME column and assigned them values in another column called 'Day of Week Value' to the datset. However, I'm still not at a final solution yet.

Thoughts?

kshah008 Moderator

## Re: Selling Days Remaining in Month

@Shevy, can you provide further insight for czmudzinski?

czmudzinski Green Belt

## Re: Selling Days Remaining in Month

I was able to solve this using the following beast mode:

```SUM(
CASE
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) >= '2016-12-16' AND DATE(`Date`) <= '2016-12-31' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-11-24' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-09-05' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-07-04' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-05-30' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 1 THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 2 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 3 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 4 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 5 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 6 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 7 THEN 0.5
END)```
Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!