Previous Month Value
What is the best way to dynamically display the previous month's value in a table based on the displayed value? The available context calculations only allow for difference from previous, etc and are not editable.
Expected output is a table with a list of months selected. Should show a measure (default distinct count) and another column displaying the selected measure's value from the previous month (relative to the date on the row, not today).
6 replies
-
Create a formula in 'Formulate', using a single 'data point'. The Data point should be your measure, then select the date attribute/hierarchy.
Instead of picking a specific element in the date, click the FX button and then choose previous member from the listing.
In script it looks like this (either one works). The wizard above produces the second one.
([measures].[transactions Sales],[transactions].[Dates full month name].lag(1))([measures].[transactions Sales],[transactions].[Dates full month name].PrevMember)
-
Just adding to 's great answer - This is not a contextual calculation, it is a semantic calculation, as you want it to be based on the data, not on the data shown in the discover.
The calculation will also work if you choose the month from a filter, for example.
-
Ok, thanks guys, that's what I was attempting to do but was hitting errors. Has anyone seen issues using previous member, etc when using custom columns?
-
Hi All,
There was an identified issue with using Lead(), Lag() and other relative position calculation functions with Custom Columns prior to Pyramid 2023.01.063.
If your build number is earlier than 063, download and upgrade your Pyramid installation to correct this issue.
Hope that helps.
Ian