How to calculate upto date sum of balance - last month upto date balance

[#1]

Hi all,

I have a excel where we upload monthly data to the existing file with the default dates on the file. The file contains default dates and balance amounts based on a relationship id. I want to calculate the sum of balances every month and also see how much has been increased or decreased from last month updated file. I have 2 years worth of data starting Jan-2016 to Current month(June-2017) data.

Example: I need to calculate all the sum starting Jan-2016 to Jun-2017 sum of sales and also find the difference between the sum of sales between Jan-2016 to May-2017.. this has to continue every month. i.e sum of all sales year to date and month to date and difference by -1 month.

Sum of sales for jan-2016 to june-2017 = 1.2bn

Sum of sales for jan-2016 to april-2017 = 1.6bn

I need to find the difference.

Please help me with the syntax.

thanks in advance

[Anil_Babu_Samineni]

May be this? I assume, you have good date format

Sum({<[Month-Year] = {">=  $(=Min([Month-Year])) < $(=Max([Month-Year]))"}>} Sales) -

Sum({<[Month-Year] = {">=  $(=Min([Month-Year])) < $(=AddMonths(Max([Month-Year]),-1))"}>} Sales)

[#3]

Hi Anil,

Thanks for your response, but its not working for me. I'm doing something wrong. Below is the expression i have written.

=if(sum({<[Month_Year] = {">= $(=Min([Month_Year])) < $(=Max([Month_Year]))"}>} and SUM(BALANCE)<0),'(' & '£'& Num(SUM(if(KEY_RANK = 1, BALANCE,0))*(-1)/1000000000 ,'#,##0.00'),
'£'& Num(SUM(if(KEY_RANK = 1, BALANCE,0))/1000000000,'#,##0.00')) & 'bn' & ')' 

Please advise what I'm doing wrong with this expression. it is saying Error in expression. Please someone help me with this.