"This Month" date range on 1st of the month?

gwsv-it

We pull almost all of our data in overnight or in the early morning hours for yesterday (or up to and including yesterday when replacing). When we use "This Month" or "MTD" date ranges, it works as expected until we get to the first of the month. At this point in time, most of our users are interested in reviewing the previous month in its entirety, rather than the current month, whose numbers won't start being pulled in until the next day.

 

 

Has anyone had a similar problem and come up with an elegant solution? A beastmode like this was the closest thing I could come up with, but when you take into account the timezone correction for daylight savings where we are located it becomes even more complicated. It also prevents users from being able to adjust the date range without having to disable a quick filter.

 

It seemed to me like this would be such a common issue that someone would have a creative solution by now.

 

Thanks!

 

 

 

BlueRooster

I don't know about elegant. But in one area of our business where the data was updated less frequently, I needed a way to always show the most recent months worth of data. I ended up creating a SQL transform where I appended a new column that searched my date field and found the MAX date there (repeating that field for every row).

 

In the analyzer I then created a date difference beastmode that basically went

CASE WHEN MONTH(`date`) = MONTH(`Maxdate`) AND YEAR(`date`) = YEAR(`Maxdate`) THEN 1 ELSE 0 END

and filtered on where the beastmode equaled one. That way you always displayed the most recent timeframe of data.

 

Maybe that will work for you? Or in other cases I've just changed to a "Last 30 days" frame so I didn't lose my abilty to change the date range.

 

Would love to hear other's ideas if they have something else.

 

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.