Cumulative Calculation Month based in a KPI object

[vyshali_v]

Hi,
  I have been trying to add a KPI object with the data that displays the cumulative sum until the current month selected on the sheet. I get the total displayed only fro the current month.

Below is the formula I tried : 
=Sum({<Month = {"$(='<=' & Max($(Month_var)))"},Year = {"$(=Max($(Year_var)))"}>}
Aggr(Sum({<Month = {"$(='<=' & Max($(Month_var)))"},Year = {"$(=Max($(Year_var)))"}>}([AOP]/1000000)),[YearMonth]))

[Vegar]

Do you really need the aggr for the KPI object? Sometimes when moving an expression from a chart to a KPI it is possible to simplify it. I've made some adjustments to your expression that you could try.

Sum({<
  Month = {"<=$(=Max($(Month_var)))"},
  Year = {"$(=Max($(Year_var)))"}
  >}[AOP]/1000000)

(If it does not work, please post the output of you variables? Are both Month and Year numeric?) 

[vyshali_v]

Thank you for your response. But I still see no change in the solution, It displays just the sum for the current selected month.
No cumulative value displayed. I've attached a word file with screenshots of the outputs I see and want.
The $(Month_var) and $(Year_var) are variables with formatted month and year values.

[petter]

I would try to add the set identfier "1" like this and make the Year not a search but a literal number element:

Sum({ 1 <
  Month = {"<=$(=Max($(Month_var)))"},
  Year = {$(=Max($(Year_var)))}
  >}[AOP]/1000000)

  What is the content of the Month_var and Year_var variables?

[Vegar]

It is hard to help without looking at the acutal application to understand the core of your problem.  Main issue is that we don't see the content of your variables. 

If you skip the use of variables you can see my attached qvf file as a suggestion on how to solve your problem. 

=Sum({<Year = {'$(=max(Year))'}, Month = {"<=$(=max(Month))"}>}AOP)

[vyshali_v]

The formula for the variables are below:
Month_Var Month(Date(Left([YearMonth],10),'MMM-YYYY'))
Year_var is Year(Date(Left([YearMonth],10),'MMM-YYYY'))

[vyshali_v]

The formula for the variables are below:
Month_Var Month(Date(Left([YearMonth],10),'MMM-YYYY'))
Year_var is Year(Date(Left([YearMonth],10),'MMM-YYYY'))


I tried the code with '1' identifier, it gives me the total AOP value irrespective of the selections I make to the Month field. I am looking for the output to be a cumulative sum value till the month I choose and my Fiscal year starts with October.