I am trying to create a roll rate, but it uses the current value from field, and the previous value from a different field.
I have two dimensions, called Tran Mia Grp and MIA_N_FINAL, and one measure called "num pol".
Tran Mia Grp is using the current "num pol" value for "3 MIA to 4 MIA", and MIA_N_FINAL needs to use the previous value for "3 MIA (#)".
So essentially I need to filter on Tran Mia Grp to get "3 MIA to 4 MIA" and filter on MIA_N_FINAL to get "3 MIA (#)".
I can then use a lookup to find the previous value for MIA_N_FINAL and do a simple calculation using "number of pol".
This was fine when calculated it once, but when I need to do it for 5 different values of Tran Mia Grp and the matching 5 different values of MIA_N_FINAL.
I create 5 sheets to filter on each of the relevant values, and got the desired results, I am just trying to create a graph of those 5 different Time Series.
I cannot attach a packaged workbook, due to privacy issues, and I am not sure I could replicate this using a different data source.
I have attached screenshots of each of the 5 worksheets, with the values I need, and a picture of the graph I previously used in Excel, as well as the calculations I used and what I would like to replicate in here.
Thank you very much.
I hope I have made sense.
3 MIA to 4 MIA and 3 MIA (#).PNG 41.0 KB
4 MIA to 5 MIA and 4 MIA (#).PNG 41.6 KB
5 MIA to 6 MIA and 5 MIA (#).PNG 41.3 KB
6 MIA to 7 MIA and 6 MIA (#).PNG 40.8 KB
7 MIA to 8 MIA and 7 MIA (#).PNG 40.8 KB
Calculation 1.png 187.0 KB
Calculation 2.png 186.7 KB
Calculation 3.png 186.7 KB
Graph.PNG 62.6 KB