Formula Question
I am using an OLAP/SSAS data model and I want to create a formula to calculate the percent change between two different values of a specific measure based on the selected value of a specific dimension.
Dimension = Academic Term (e.g. Fall 2018, Spring 2017) This value would be selected in a slicer.
Measure = Total credit hours attempted (a numeric value)
What I would like to do is construct a formula that calculates the percent change in credit hours attempted between the selected term and the same term three years prior.
Formula = (Total credit hours attempted for selected term – total credit hours attempted for the same term three years prior) / total credit hours attempted for selected term.
Can anyone tell me if this is possible and, if so, how to construct this in the Formulate module?
Thanks
2 replies
-
Hi Chris,
I'll explain how to get this result and add attached screen shots using "Sales Amount" as your "Total credit hours attempted" and "Calendar Year" as "Academic Term".
Create a new formula with 3 data-points, where 2 of them are the same (A-B)/A
A = select Total credit hours attempted
B = select Total credit hours attempted, and in the Academic Term select the functional selection of "lag 6"
The lag function uses the member that is before the current one.
So for Fall 2018:
lag 1 = Spring 2017
lag 2 = Fall 2017
lag 3 = Spring 2016
...
lag 6 = Fall 2015
If you have Summer in between, just change the lag number accordingly.
Save your calculation as a measure and set the format string to be percent.
Use it in your report.
See attachments for some visual instructions.
Hope this works for you,
Imbar -
Thank you very much!
