Hi dgf,

There are a number of different ways you can achieve this. To find the best solution for your specific requirements we'll need to know a little bit more about how you want to use the calculated value in your analysis. 

It could be one of these:

1. In an analysis you have months in rows or columns and for each month you want to calculate two months back / one month back
2. In an analysis you want to select  a month in a slicer and calculate two months back / one month back from that month
3. In an analysis you  you want to calculate two months back / one month back from today's month
4. Some other scenario

Let us know how you need to use it and we can provide an appropriate solution.

Ian

Hi dgf,

The first thing we need to do is establish the current month and the previous month. There are many ways we could do this, but an elegant and simple way is to use your existing data to do this, creating a dynamic set consisting of the current and previous month by identifying all the months that contain data for your Measures concerned, then taking the last two. Of course, as more data is added to your database each month, the list will move forward with your data.

In my examples here, I'm using the hierarchy "Date Full Month Name" (you must have something similar) and the measures Returns and Units for your Active Orgs and Total Orgs respectively.

Using Formulate, create a new List against your Model. Double click or drag the "Standard List" block onto the canvas:

Choose the "Filter" section and drag on the "Non Empty" filter. Choose your Measure "Active Orgs", in my case Returns. The non empty filter will remove any Months that do not have a value for Active Orgs. I'm assuming here that you do not have any data for Active Orgs for future months. This will leave a list of months up to and including the current month.

Again from the filter section drag on the "Last" Filter and set its value to 2. Click on the Pyramid icon to process the list. You will see the last two months listed in the Preview Pane, shown in the yellow box. Save the list as "Last two Months".

Now for the % calculations.

Create a new Formula. We need to divide one data point by another to get the % needed. Double click or drag the "Data Point" Element Block onto the canvas. I don't have your Org Measures, so I am going to use Returns as the active Orgs and Units as the total Orgs (I am in fact calculating the % of units sold that are returned). 
Click on the Data Point block and from the bottom panels choose the Measure for the denominator, in my case Returns, in your case Active Orgs. Type a division symbol, /, then add another data point and choose the divisor, Units in my case and total active Orgs in yours. Set the format to 0.00% as shown in the yellow box.

To calculate the Delta, we need to subtract the previous month's % Returns (%Active Orgs for you) from the current month for each month in the list. We can do this using Member functions in the calculation.

Create new Formulate formula. Drag on a Data Point block. For the Measure, chose the % Returns (% Active Orgs) we calculated previously by clicking on the small fx folder icon just under the Select Elements shown here in blue to reveal the calculated Measures.

We need though to tell Pyramid which % Returns we want as the numerator. and which as a denominator. Click on the Full Month Name hierarchy. You will see in the top right of the members pane a small "fx" icon, shown by the green box. Click on this and check the "Current Member" option.

Type a minus sign, then repeat the above step, but this time in the fx panel for Full month Name choose "Previous Member". Set the formatting to 0.00% and save as Delta.

Phew! We're now ready to put all this together.

Create a new Discover view against your model. Put Full Month Name in the Column Drop Zone. Deselect all the Members in the Elements panel and select the Last Two Months list.

Add whatever you need  in the rows, I'm using Product Category here.

Now choose your Measures in the order you want them, here as Returns, % Returns and Delta.

Because we're using the actual members, May 2023 and Jun 2023 form our dynamic list, Pyramid knows what the previous month to May 2023 is from the information about Full Month Name in the model, so is able to calculate the Delta from Apr 2023 to May 2023 correctly, without Apr 2023 having to appear in the Discover view..

It's been a bit of a journey to get here, but hopefully this has helped you in how to approach these types of requirements. There are, of course,  any number of ways to solve this problem. Rather than have the Months in the view, you could create six individual Measures, but that is more work and not as elegant in my opinion.

Hope that helps!

Ian