How to write the following expression ?

[suvechha_b]

Hi All ,

How to write the following expression ?

The expression

RangeSum(Before(Sum(Aggr(if(Measure='Quantity',      SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} Quantity),
if(Measure='Value',      SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} [Sales Value]),
if(Measure='AIMS Units', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Quantity]),
SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Sales Value]))))
, Measure, [Calendar Month], Month,[Fiscal Year])), 0, ColumnNo()))

How to calulate the PY Actual ?

sample attached?

[sunny_talwar]

Check the attached

1) Changed the expression

2) Changed the variable definition for vPrevFiscalYear

[suvechha_b]

But the Prev Yr Actual is not summing up like the below:

The report should , show from Jul to Sep

and the Total Units should be

Jul           Aug                     Sep

5041        5041 +8124        5041 +8124+5072

And if I dont select any [Fiscal Period ]

The output :

Jul           Aug                     Sep                                               Jun

5041        5041 +8124        5041 +8124+5072 .........             5041 +8124+5072 .........  +9007

[sunny_talwar · Accepted solution]

My bad, forgot to add the RangeSum(Before())

RangeSum(Before(Sum({<[Fiscal Year]={$(vPrevFiscalYear)}>} Aggr(if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='Quantity', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} Quantity), if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='Value', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'A'} >} [Sales Value]), if(Only({<[Fiscal Year]={$(vPrevFiscalYear)}>} Measure) ='AIMS Units', SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Quantity]), SUM({<[Fiscal Year]={$(vPrevFiscalYear)},[Calendar Month]={'<=$(=Max([Calendar Month]))'},[Type Indicator] = {'AIMS_SALES'} >} [Sales Value])))) , Measure, [Calendar Month], Month,[Fiscal Year])), 0, ColumnNo()))