Actually I am looking for the sum of customer balances by year like if i select May-2011 then expression can sum all the values in 2011 before and in may-2011 excluding june-2011 onward. similarly with every month.

Thanks for the reply Toni, I am attaching one sample of my project. I am having problem on disabled field COF Final... Please see it on expression tab and if you have any solution then let me know... Thanks in Advance.

I may be missing something but is this not a simple pivot with Months as the dimension along the top and then Set Analysis in the expression to limit to the max year and months prior to the max selected?

Something like:

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month=>}Value)

I've also attached an example that may help.

By extending the example to also be aware of the Prior year you could put in a YTD prior year comparison with variances.

That looks like it should work, but will divide every month by the number of months - so your average will be correct but not the monthly columns. The other thing that would not be as your example above is having both a Total and an Average column. This is not easy (possible?) in a pivot.

What you could do is change it to a straight table and have a separate expression for each month, and then separate expressions for the Total and Average fields. You could then change the background and text settings for those expressions to make it clear they are totals. Finally you would need to conditionally show or hide the months (unless you wanted to show the zero future months).

Generally it is really not recommende to use to many nested IF statements, since this can have serious impact on the application performance. You are much better of if you can find a solution using set analysis and/or Aggr().

An other option can be to do some additonal calculations in the load script, so that parts are statically precalculated and only needs to be sumed in the expression. This would be of some performance gain in case you run larger data volumes or a slower machine.

It would be easier to advise on a good approach if you attach a sample of you solution, so that the data model can be evaluated.