7 Replies Latest reply on Mar 6, 2017 1:45 PM by Peter Haenggi

# Calculating MTD Through Previous Saturday

Hey Guys,

Got a question I was hoping someone could answer on a MTD calculation. I am trying to calculate MTD based on the previous Saturday. For example, let's say today is 02/28/2017 I want to be able to calculate through last Saturday a MTD for February - so only MTD of the 1st through the 25th of February.  I can't seem to quite get the formula to work right for me.

For example a formula such as this:

THEN ([REVENUE])

END

Thanks!!

• ###### 1. Re: Calculating MTD Through Previous Saturday

I would approach it like this:

SUM(

IF  [BUSINESS_DATE] >= DATETRUNC("month", datetrunc('week',today(),"Saturday")) AND

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.

• ###### 2. Re: Calculating MTD Through Previous Saturday

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?

Thanks again!

• ###### 3. Re: Calculating MTD Through Previous 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:

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.

• ###### 4. Re: Calculating MTD Through Previous Saturday

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.

• ###### 5. Re: Calculating MTD Through Previous Saturday

It also seems to spit out the exact same numbers year to year when I get the formula to work.

• ###### 6. Re: Calculating MTD Through Previous Saturday

Time to upload a sample workbook.  (Let me know what version of tableau it is on.)

• ###### 7. Re: Calculating MTD Through Previous Saturday

Joe, I have figured it out.  Had some issues with my joins, which was causing an issue! Thanks for all your help, much appreciated!!

Pete