I hope you can help me with my little problem.
I am using BI version 22.214.171.124.5 and would like to create a report, where there are going to be two columns (along with a number of others):
1. Number of customers for the selected month (using a dashboard prompt)
2. Number of customers for the month before the one selected above
It is easy to create #1 with COUNT(DISTINCT "Dim Sender Customer V"."Sender Customer Id"). I'm not sure how I can create #2... any ideas?
You need to use Ago function
AGO(Count of customer id, Month level , 1).
You should have a Time dimension configured in rpd with Month as a level. Then you can create a measure in RPD as AGO(Count of customer id, Month level , 1).
This will give you Month Ago value.
Thanks for the very fast reply. We had the same idea as well, but apparently that might slow down BI (I am assuming this is due to the huge number of customers involved).
Is there any way to do this without the AGO function (or RPD all together)?
You can find previous row measure value using MSUM function.
MSUM("Sales Facts"."Amount Sold",2) - "Sales Facts"."Amount Sold"
Replace "Sales Facts"."Amount Sold" with your measure and make sure your report is properly sorted by Month.
Edited by: Maqsood Hussain on Dec 6, 2012 11:35 PM
OK, good, one further request: what if I want to find the difference between the two months and display them in a third column? There are tons of functions to sum stuff, but I couldn't see anything to calculate differences.