How about this approach?
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.
trial_last_year_9.2.twbx 169.3 KB
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.
Sorry for late reply.
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.
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(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.
sum([Qty(psc)])/SUM([Sheet1 (Book1) (copy)].[Qty(psc)])-1
trial_last_year_2_9.2.twbx 186.9 KB
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.
Thanks in Advance
trial_last_year_2_9.2.twbx 99.7 KB
1 of 1 people found this helpful
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!! The video solved my issue in several workbooks. Thanks a ton
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
Date function is very useful but sometimes tricky.
I'm also still keep learning.