9 Replies Latest reply on Mar 21, 2016 6:39 AM by Shinichiro Murakami

# Month over Month this year to last year calculation

Hi ,

I have to build a line graph as below .

Across yearmonth axis i have to plot the sales qty for the selected period and category.

LY sales quantity calculation doesn't seem to happen properly . i need to show LY same month sales qty value in the graph for that particular category

and also the growth percentage.

Referred the below links,But not happening in my scenario

Any clue what I am missing ....

• ###### 1. Re: Month over Month this year to last year calculation

It's a little bit tricky to show LY value with only filtering this year.

Not impossible, just tricky.

If bellow chart is O.K. it's much easier.

Put month(date) to column and year(date) to rows.

Then Put "date" as filter as range, which is shown as quick filter with slider.

Thanks,

Shin

9.2 attached.

1 of 1 people found this helpful
• ###### 2. Re: Month over Month this year to last year calculation

Thanks for your reply...But I need to see the values along year,month axis,because if they want to see fiscal year TY sales....LY sales and Growth% ..they might select April2014 - march2015 in Date period filter.

• ###### 3. Re: Month over Month this year to last year calculation

Keerthana,

Here is a solution.

Duplicate data source.

Rename "Date" to "Date original" in 2nd data source (Orange color source)

Duplicate "Date Original" and rename to "Date" // This helps to link primary data source and secondary data source automatically.

Change new "Date" field's formula as below.

[Date]

dateadd('year',1,[Date original])      // This links primary datasource's 2016/1/1 and secondary datasource's 2015/1/1.

On above chart, still Y-Axis is not synchronized, so add one more calculated field to forth bottom chart's Y-axis same as top.

Create calculated field on 1st datasource.

[Max_Line]

max(sum([Qty(psc)]),SUM([Sheet1 (Book1) (copy)].[Qty(psc)]))

Put this field to mark of All.

Add reference line for both Charts.

To show delta, create calculated field on 1st datasource.

[Delta%]

sum([Qty(psc)])/SUM([Sheet1 (Book1) (copy)].[Qty(psc)])-1

Thanks,

Shin

9.2 attached.

1 of 1 people found this helpful
• ###### 4. Re: Month over Month this year to last year calculation

Thanks Shin !! For considering my post and a very  clear explanation with screenshots.

Sorry for bothering you.. I have couple of doubts here

1. In the graph I changed the filter from days to Months...Now when I set the filter from Jan 2015 -Dec 2015 my graph shows line only from Feb 2015 to Dec 2015.

2. My original data is very big with four years of sales data and n number of calculated fields

Is it advisable to duplicate the source and have the visualization....

3. In the first graph idea u gave me which u plotted(below screenshot) ..Is there a way to preserve the '% of difference from ' while i have year as filter.

i.e, when i uncheck year 2013......... growth% in the year 2014 disappears.

Keerthana

• ###### 5. Re: Month over Month this year to last year calculation

Hey, is this what you're after?

I'm a bit lazy today but let me know if you need explanation...

1 of 1 people found this helpful
• ###### 6. Re: Month over Month this year to last year calculation

1. I don't know the technical background yet, but you can achieve it as below.

2. According to my experience, duplicating data does not harm file size. Don't know the speed performance, but should be O.K.  Anyway you can try.

3. Instead of filter out, you can hide.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 7. Re: Month over Month this year to last year calculation

Thanks Shin!! The video solved my issue in several workbooks. Thanks a ton

• ###### 8. Re: Month over Month this year to last year calculation

Yes this is my requirement....Thank you so much for the help and introducing window function..i would have never thought of it ...... But again i had the date filter issue but was able to solve with help of video byShinichiro Murakami...

Awesome Community.........Happy learning

• ###### 9. Re: Month over Month this year to last year calculation

Date function is very useful but sometimes tricky.

I'm also still keep learning.

Thanks,

Shin