How to get current dimention value in set analysis of chart expression?

[#1]

Hi community,

I'm trying to make a chart:

- dimention  is MonthYear

- expression is Sum(Sales) for the Customers with RegistrationDate < start of current  MonthYear in a dimention (they have registered before start of current month)

I have tryed

sum({<[CustomerID]={"=([CustomerRegistrationDate]<MonthStart(Min(MonthYear)))"}>} Sales)

but it filters out all Customers who have registered before the first one month in a chart, so it's not what I need if there are more than one month in a chart.

So I need to refer to a current value of dimention someway instead of min().

Any help is appreciated.

[Gysbert_Wassenaar · Accepted solution]

When you use set analysis the set is calculated at the chart level, not at the row level. So your set analysis expression won't work since you're using MonthYear as a dimension. I think you'll need to add an extra table in the script that links each MonthYear value with all the CustomerRegistrationDates that lie before that MonthYear value.

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

[#3]

Thank you, Gysbert, for the explanation of fundamentals of set calculation and the detailed issue about rolling totals.

From this i've got an idea to use already linked fields to filter data and it works:

sum({<[OrderID]={"=(MonthStart(OrderDate)>MonthStart([CustomerRegistrationDate]))"}>} Sales)

Instead of filtering out Customers I filter out Orders placed in the month of registration.