I would approach it like this:
IF [BUSINESS_DATE] >= DATETRUNC("month", datetrunc('week',today(),"Saturday")) AND
[BUSINESS_DATE] <= datetrunc('week',today(),"Saturday")
then [Revenue] END
So this: datetrunc('week',today(),"Saturday")
...moves from TODAY() to the previous Saturday.
The uppercase DATETRUNC moves the Saturday value to the first of whatever month the lower datetrunc computes.
You might need to wrap those DATETRUNCs in DATE() because I find that DATETRUNC gives me a date-time value, and you'll be comparing against values set to 12:00AM, and that might mess you up.
Joe that is awesome! It works great!! Thank you for the help.
One more question, what if I wanted to go back to look at same time last year with that same logic?
DATEADD("year", -1, datetrunc('week',today(),"Saturday")
this will take the result of the "Saturday" operation and subtract exactly one year.
Now, the question you might need to address is whether you want to look at the same specific string of dates, or if you want to look at MDT up to a Saturday, one year ago.
If the latter, then you would do this instead:
datetrunc('week',DATEADD('year', -1, today()),"Saturday")
This will subtract the year first, and then do the "Saturday" operation.
And then, of course, you'd plug this into the SUM( IF ...) from earlier.
Joe thanks again - big help!! Could you write this formula out for me in correct syntax? Anytime I try to include this logic I receive parsing errors for some reason. I'm kind of new to some of these formulas, so probably just not following the logic exactly.
Time to upload a sample workbook. (Let me know what version of tableau it is on.)
Joe, I have figured it out. Had some issues with my joins, which was causing an issue! Thanks for all your help, much appreciated!!