Referencing Previous Column and Row using Measures in Power Pivot

[Anonymous]

Hello,

I am currently trying to create a measure (in Power Pivot and Power Query) that will allow me to reference a different row and column from the one I am currently working on (see question at very bottom). Here is the scenario:

At the beginning of Month 1, I acquire 0.5 customers. Over the course of the month, I lose 0.0042 customers. As such, at the end of Month 1, I have (0.5 – 0.0042) = 0.4958 customers left

In Month 2, I gain another 0.5 customers. This means that the Total Customers at beginning of month is 0.9958 ((Customers at end of previous month + Customers Acquired at beginning of current month) (0.4958 + 0.5)). Consequently, I lose another 0.0083 customers in Month 2, leaving my total number of customers at 0.9875.

This process repeats for all subsequent months.

The named columns are all measures calculated from the data imported using Power Query.

• “Total Customers at beginning of month” is calculated as (“Customers Acquired at beginning of Current month” + “Customers at End of Previous Month”)
• “Customers at End of Month” is calculated as (“Total Customers at beginning of Current month” - “Customers Lost during Current month”)

My question is: Is it possible to reference a different row and column of a pivot table when making calculations using measures?  When I tried it in the past, I have run into the issue that I cannot reference “Customers at End of Previous Month” from the “Total Customers at beginning of current month” (different row, different column) while also using “Total Customers at beginning of current month” is being used to calculate “Customers at end of current month” (Circular reference).

I would be very appreciative of any ideas or suggestions!

Thanks in advance!

[lbendlin]

Measures in a matrix visual are recalculated for each individual cell, for the row totals, the column totals, and for the grand total.

You can do this kind of cumulative calculation, but you have to do it independently for each cell. You cannot reference visual cells in measures.

General guidance: "Think like the Grand Total" . More often than not a formula that works for the grand total will also work for the subtotals and individual cells.

If you like more help please provide sample data in usable format (not as a picture - maybe insert into a table?).

[Anonymous]

Hello @lbendlin ,

Thank you for your help and for the explaination! 

I have updated my spreadsheet to reflect the new information. (Please see below screenshot).

Also, by "cumulative calculation," do you mean something like the EARLIER function? That is how I have set up my measures so far.

Once again, I would appreciate your help and any feedback you can provide on how to update my current measures!

Thanks!

[lbendlin]

The use of EARLIER is discouraged. Instead, use variables to protect your results from filter context transitions.

But - if it works for you then go with it.