ok here is an sample image of the original report.
My Original Requirement is little complex to duplicate as an workbook.
So please use my comments for details.
I have a total of 50 + different measures for an ivr self service monthly report.
we have to show the calls for all the months of the current year (which we are able to do).
in the next tab near to December we have to show the total for the current year for all the 50+ rows (which is easy with the inbuilt function)
the next tab should show the Calls for the year minus one the current year.
(this we did by simply duplicating the sheet and selecting the previous year from the filter. but this method is not applicable to real time right, as once we have published to the server, the reports should dynamically open for the current year.)
this is where we are struggling) how to dynamically set the filter to minus one year for the previous year total (needed for all the 50 rows)
and the last tab should show the % Change from previous year to the current year (needed for all the 50 rows).
I need formulas to calculate the previous year total and % change and also what kind of date range filter should be used here
for this kind of monthly self service report (an year filter or an date picker or any date parameter)
What you could do is to build measures for Jan - Dec, and then 3 measures for your total columns
I have attached an example based on the superstore dataset which then looks like the following. Would that work?
Book1.twbx 2.5 MB
Thank you, For YTD Previous this worked for me.
YTD Previous = IF YEAR([Date Range]) = [Year Filter] - 1 THEN [Attempts] END
How to show up,down and equal indicators as well before/after the % Change Tab?