Line Chart Showing Monthly Values for Fiscal Year Lines
I need to create a line chart showing the monthly values for each of several fiscal years as a line chart. The user wants to be able to see lines comparing each month in each fiscal year. For example, on the X-axis would be each month in the fiscal year, January to December, and on the Y-axis would be each fiscal year, FY21, FY22, FY23. This would allow the user to see a line for each fiscal year's January through December, and allow them to compare each year's results against the other years while also seeing seasonal/monthly variations over time.
I don't believe I can set years to one axis and months to another axis using the Model's time. So my plan is to create a list for Prior Year, Current Year, Next Year, etc. and then use a module to map what FY year the prior/current/future years are. This would be to make the chart sustainable and easy to update as years go by. I would then dimension the data module for the line chart by the list of years and have each of the module's line items dimensioned by month. I believe I can then create IF/THEN formulas for each line item to identify the value for each of the years at each month.
If there is a cleaner/easier way to accomplish what I am trying to do I would love to hear it. Or any feedback really.
Thanks!
Tim
Best Answer
-
I believe you are on right track. Having two different lists for Months and Years is the only Way forward. The only thing I would like to highlight is that Don't write Too many IF ELSE statements, see if it can be done using LOOKUP function and/or SUM function based on the mapping.
Hope that helps
Misbah
2
Answers
-
Thanks Misbah. I will try the Lookup/Sum functions first.
Tim0 -
Hi Tim, found this as I also have this exact requirement. Just checking if you found a simpler method to create the chart?
0
