4 Replies Latest reply on Jul 3, 2018 11:23 AM by Jessica Reason

Calculated Field: Using two different measures and two different years

Hi!

I can't figure this one out, and the other responses on the community are missing the mark because they say you need to do a Date calculation and put it on the filter (I can't do that). I am attaching a workbook (using superstore data) and it details what I am trying to do.

But here is what I am trying to solve: I have two different measures and I want to create a monthly calculated field, but the catch is one of the measures should use 2018 data, and the other should use 2017 data.

See attached for a jpeg and a packaged workbook. It uses the Sample-Superstore Data.

Thanks for any advice you have!

• 1. Re: Calculated Field: Using two different measures and two different years

Several ways to do this.

In the attached I used LOOKUP, and I actually didn't need to create the two calcs that I did.  I just really needed Calculation1.

I could have done this with LODs too.

PS:  Your JPG shows two different calculations.  One subtracts.  The other divides.  I did the subtraction.

• 2. Re: Calculated Field: Using two different measures and two different years

Can I ask one more question of you:

-what if I wanted to Sum sales of 2017 Trinkets, but only through the end of the most recent completed month (June)?  So I want to SUM(TRINKET Sales) for Jan-Jun 2017, got any ideas what that formula should look like? Then once July is completed, then July should be added to the calc.

I want YTD sales, but offset by a year.

Thank you!

• 3. Re: Calculated Field: Using two different measures and two different years

In the attached I modified the 2017 trinkets calc to grab only up through the month prior to today.

This may or may not be sufficient for what you need.  If today is the last day of the month (such as July 31), would you want the current month to count?  We can do something more than just using TODAY() if necessary.

Look at the calc I displayed in the sheet title;  [last order date in data source].  This tells us the largest [order date] in the data.  The sample data goes through Dec 2018, but your real data probably doesn't exceed TODAY(), and might likely lag TODAY() by a day if your closing process works that way,  (That's how it works where I work.)  So today I might only have data through June 29, and therefore today I don't have a closed month of June, and your requirement might want me to grab only through the end of May.  (Did I explain that well?)

Anyway, you can tell that trinkets calc where to cut off the data.  Depending on what your business does, it might be based on TODAY() or on some other function.

• 4. Re: Calculated Field: Using two different measures and two different years

Thank you Joe! Very helpful and much appreciated!