11 Replies Latest reply on Jun 27, 2016 5:51 AM by Kyle Boyce

# Calculating/Visualizing Month over Month and Year over Year %'s

Hello,

I have attached a sample of the view I'm looking to produce.  I am trying to show Month over month and year over year for a given measure.  However, I don't want to have to show the #'s for all 13 months prior to the current month in order for Table Calculations and the relative position to work.  Is there a way to show only the current month's metric and the Month over Month and Year over Year calculation in simply 3 columns.

See attached for more details

Thanks

• ###### 1. Re: Calculating/Visualizing Month over Month and Year over Year %'s

You can just hide all the previous months (select month names in the view and right-click -> Hide). They would still be there, allowing table calc to work, but will not be visible.

• ###### 2. Re: Calculating/Visualizing Month over Month and Year over Year %'s

Thanks for the tip.  This is actually good to know.  Sorry for the additional ask, but when you hide the dates, you make them "unselectable" in the Quick Filters.  I was hoping to make the date selection dynamic, yet allow for a Month-over-Month and Year-over-Year pull.  For example, if I want to select a criteria for March-11, it will give me Month-over-Month and Year-over-Year for that particular month.

• ###### 3. Re: Calculating/Visualizing Month over Month and Year over Year %'s

Brent,

how would you define YoY and MoM?

One of them is the "same month previous year" - what is the other one, and which is which, as applied to a single month? Is it the "previous month same year"?

• ###### 4. Re: Calculating/Visualizing Month over Month and Year over Year %'s

Hey Dimiri,

MoM would be the previous month same year (For May '11, I want to compare to April '11)

YoY would be same month previous year (For May '11, I want to compare to May '10)

Thanks

• ###### 5. Re: Calculating/Visualizing Month over Month and Year over Year %'s

Brent,

have a look at the sample workbook attached - does it solve your problem?

The solution involves table calculations, parameters and column/row hiding. Post here if you need detailed instructions.

• ###### 6. Re: Calculating/Visualizing Month over Month and Year over Year %'s

Hi Dimitri,

Could you tell me how to get the last step of your solution (final tab) so that all the other months aren't visible?

Thank you!

• ###### 7. Re: Re: Calculating/Visualizing Month over Month and Year over Year %'s

Allison, Dimitri is asleep right now, so I'll jump in:

Repeat for the MONTH(Date) pill.

Hope this helps.

--Shawn

3 of 3 people found this helpful
• ###### 8. Re: Re: Calculating/Visualizing Month over Month and Year over Year %'s

Ah thank you!! I would've never thought to do that

• ###### 9. Re: Re: Calculating/Visualizing Month over Month and Year over Year %'s

Yeah, it would be nice if there were some sort of visual cue on the pill. You can tell when something is being hidden by clicking the triangle and looking for "Show Hidden Data". So if you see something like this in the future, check for that first. And now you know how to do it yourself! (Gotta love the forums.)

Cheers,

--Shawn

• ###### 10. Re: Calculating/Visualizing Month over Month and Year over Year %'s

This was an amazingly useful approach to me!

I had to show only a running sum of a value, and yet display it only up to the month defined by a date parameter (and be able to see the progression as I moved a slider).

Thanks a lot, Shawn Wallwork! Shouldn't this be marked as the right answer?

• ###### 11. Re: Calculating/Visualizing Month over Month and Year over Year %'s

I'm using the LOOKUP function to go 12 months back and getting the difference between this month and that one, but I show all the months. so I've manually hidden the months I don't want, but sometimes people put data in the system for a month i haven't selected to hide, so it shows up.

is there an additional filter i can put on the worksheet to hide all month except this current month, but still keep the calculation? or would i have to change the calculation?