Current Date to EOM Filter

Shumilex

Hi Team,

I'm trying to filter some data but what I want it to do is to filter based on the date you're going into the report and end of the month. Example if I go in today it would show me the pending arrangements for 8/9 to 8/31 and tomorrow would be 8/10 to 8/31, etc.

I'm thinking I have to do a beast mode for this, but let me know what would work the best.

Thanks,

Shumari

MarkSnodgrass

I would use a combination of the date range filter and a beast mode, but you could also do it all with a beast mode if you wanted.

Set the date range filter to This Month

Create a beast mode that looks like this:

CASE WHEN 'arrangementdate' >= CURRENTDATE() THEN 'Include' ELSE 'Exclude' END

Drag the beast mode into your filters and filter to Include

If you wanted to do it all in beast mode, you can leave your date range filter to All Time and then use the following beast mode:

CASE WHEN 'arrangementdate' >= CURRENTDATE() AND MONTH('arragementdate') = MONTH(CURRENTDATE()) THEN 'Include' ELSE 'Exclude' END

GrantSmith

@Shumilex

To add my two cents I'd recommend going a bit further in the beast mode as MONTH will return the month number so if you're going multiple years in the future you'll get unexpected results. I'd recommend the LAST_DAY function instead which returns the last day of the month for a given date.

CASE WHEN `arrangementdate` BETWEEN CURRENTDATE() AND LAST_DAY(CURRENTDATE()) THEN 'Include' ELSE 'Exclude' END

You may also find this write up I've done in the past about BOM/EOM and BOW/EOW calculations:

https://dojo.domo.com/discussion/52687/domo-ideas-exchange-beast-modes-first-last-days-of-the-month-week

CC @MarkSnodgrass

MarkSnodgrass

I would use a combination of the date range filter and a beast mode, but you could also do it all with a beast mode if you wanted.

Set the date range filter to This Month

Create a beast mode that looks like this:

CASE WHEN 'arrangementdate' >= CURRENTDATE() THEN 'Include' ELSE 'Exclude' END

Drag the beast mode into your filters and filter to Include

If you wanted to do it all in beast mode, you can leave your date range filter to All Time and then use the following beast mode:

CASE WHEN 'arrangementdate' >= CURRENTDATE() AND MONTH('arragementdate') = MONTH(CURRENTDATE()) THEN 'Include' ELSE 'Exclude' END

GrantSmith

@Shumilex

To add my two cents I'd recommend going a bit further in the beast mode as MONTH will return the month number so if you're going multiple years in the future you'll get unexpected results. I'd recommend the LAST_DAY function instead which returns the last day of the month for a given date.

CASE WHEN `arrangementdate` BETWEEN CURRENTDATE() AND LAST_DAY(CURRENTDATE()) THEN 'Include' ELSE 'Exclude' END

You may also find this write up I've done in the past about BOM/EOM and BOW/EOW calculations:

https://dojo.domo.com/discussion/52687/domo-ideas-exchange-beast-modes-first-last-days-of-the-month-week

CC @MarkSnodgrass

Shumilex

Hey Guys,

Thanks for the assistance @MarkSnodgrass and @GrantSmith, the below is what I used:

CASE WHEN `Date_Arrangment_Set_for` >= CURRENT_DATE() AND `Date_Arrangment_Set_for` <= LAST_DAY(CURRENT_DATE()) THEN 'Include' ELSE 'Exclude' END

Not sure why but when I was using BETWEEN based on Grant's suggestion it did not want to accept it in the beast mode and when I used Mark's it gave me a bit more in the amount. I'll figure out why later but for lack of time this is what worked for me and I validated the data and the amount matches. I think it's a hybrid of both of your suggestions. Thanks very much! 😊

ST_Superman

@Shumilex - the Calculated Field syntax does not like the BETWEEN clause. The formula that you provided should work for you. However, I would recommend being more descriptive with your filter expressions. I know that for the current use case, you are only interested in including future dates in the current month. But what happens when you want to see what has already happened this month?

I would write something like this:

CASE 
  WHEN `Date_Arrangment_Set_for` < CURRENT_DATE() THEN 'Historic'
  WHEN `Date_Arrangment_Set_for` = CURRENT_DATE() THEN 'Today'
  WHEN `Date_Arrangment_Set_for` > CURRENT_DATE() THEN 'Future'
END

You can now use this field as a filter. In your case, you would choose to include 'Today' and 'Future', or to exclude 'Historic'. I would then follow @MarkSnodgrass suggestion and include a date filter on the card for current month. This way, if someone wanted to see remaining events for the current quarter or year, they just need to change the date selection from month to quarter, etc.

You could even add a quick filter on the case statement so that if someone wanted to decide to look at historic vs future, they could do so by changing the quick filter.

This would just give your report more flexibility to allow more exploration, IMO