Business User want to See last 12 Months Running Total Dynamically?
Calculations to be used:
Index, Size and Running Total
Date Set Used : Sample Superstore
Let’s Build the View for Running Total:
Connect to Superstore Data
Step 1: Drag Order Date to Columns
Step 2: Right Click on the Year(Order Date) and change the format from Year to Month-Year
Step 3: It Should Look Like this
Step 4: Again right click on the Month(Order Date) and change the data points from Continous to Discrete
Step 5: It Should Look Like this
Step 6: Drag Sales Measure to Rows section
Step 7: Change the SUM(Sales) to Running total
Step 8: Here comes the Tricky part, User wants to view Latest 12 Months dynamically
error most people do:
Validation : IF datediff(‘month’,[Order Date],today()) <=11 and datediff(‘month’, [Order Date],today())>=0
then “True” else “False” end
Above calculation will give last 12 Months from Today.
We will use this Calculated field in filter and select “TRUE”
It Should Look Like this
But if you see the running total December-2016 value is “15,63,986”, once you applied the filter your December-2016 value is “96,999”. My requirement is running total of Last 12 Months which my December 2016 value should start from “15,63,986”, not from “96,999”.
Becuase when you apply the filter, last 12 months data will come to worksheet after filtering out. But we don’t want that scenario we need all the Months data and want to show last 12 months in the worksheet.
The correct way to Do:
–> viz which date is sorted Descending ( Max date to Min date)
–> Now Write Index Calculation
–> Click on the Index measure Created and select “Convert to Discrete” and Drag next to months Pill
–> You will get numbers from 1 to 47 ( which index usually does like Row number or Serial Number in a partition )
–> remove the Index Field ( its just to check whether the numbers are working Properly or not)
–> Calculation to restrict last 12 months:
Validation correct : if size()-index < 12 the “True” else “False” end
— Drag the Created Field to Filter and select “True”
Now we got the Running total as we want and as well last 12 Months and it is dynamic
The calculation which we created will apply to data Coming in to view and we are restricting visibility by 12 Months only.