Sum Last Three month

[LearnMeExcel]

Hi

I am trying to make this simple calculation
This is sample data
[TABLE="width: 134"]
<tbody>[TR]
[TD="width: 79"] Row Labels
[/TD]
[TD="width: 100"] Total Sales
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] January
[/TD]
[TD="width: 100, bgcolor: transparent"] 645,142
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] February
[/TD]
[TD="width: 100, bgcolor: transparent"] 938,921
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] March
[/TD]
[TD="width: 100, bgcolor: transparent"] 546,220
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] April
[/TD]
[TD="width: 100, bgcolor: transparent"] 865,123
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] May
[/TD]
[TD="width: 100, bgcolor: transparent"] 1,010,323
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] June
[/TD]
[TD="width: 100, bgcolor: transparent"] 702,331
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] July
[/TD]
[TD="width: 100, bgcolor: transparent"] 1,014,847
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] August
[/TD]
[TD="width: 100, bgcolor: transparent"] 1,596,815
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] September
[/TD]
[TD="width: 100, bgcolor: transparent"] 1,474,167
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] October
[/TD]
[TD="width: 100, bgcolor: transparent"] 1,604,745
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] November
[/TD]
[TD="width: 100, bgcolor: transparent"] 2,383,518
[/TD]
[/TR]
[TR]
[TD="width: 79, bgcolor: transparent"] December
[/TD]
[TD="width: 100, bgcolor: transparent"] 3,424,852
[/TD]
[/TR]
[TR]
[TD="width: 79"] Grand Total
[/TD]
[TD="width: 100"] 16,207,005
[/TD]
[/TR]
</tbody>[/TABLE]

I want Measure to Calculate the Sum Of last four months
For example
In May
[SumOfLastFOurMonth]=2995406
And same for other month
This data for 2013 , that is mean for Feb [SumOfLastFOurMonth] = Oct2012+Nov2012+Dec2012+Jan2013
I hope my case is clear
Thank you

 

[scottsen]

I am going to assume you have a Calender table, that has a MonthId, because that is fairly easy to achieve, and makes problem easier. The MonthId will not reset at each year. So, If Dec2012 is MonthId=12, then Jan2013 is MonthId=13.

=CALCULATE(SUM(FactTable[Sales]), FILTER(ALL(Calendar), Calendar[MonthId] < MAX(MonthId) && Calendar[MonthId] > MAX(MonthId) - 4)

Something like that... though, I'm not sure how well that will behave w/ Years or Days on rows, instead of Months...

 

[LearnMeExcel]

i will test it an tell you what is feed back
nay way thanks

i wll addolumn For month number in my data

 

[Aladin Akyurek]

i will test it an tell you what is feed back
nay way thanks

i wll addolumn For month number in my data

Try something like outside the B colum...

=SUM(OFFSET(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)),0,0,-MIN(3,COUNT(B:B))))

 

[LearnMeExcel]

Scott
Doesn't work
in RowLabel i have month Name as above
in Year "Slicer Vertical" i select 2013
and in my Calendar i added New Column with name MonthId it is start from MonthId 1 TO 60 Month in My Calendar
and then i use this formula

=CALCULATE([Total Sales],
                        FILTER(ALL(Calendar),
                                                            Calendar[MonthId] < MAX(Calendar[MonLongN]) && 

                                                            Calendar[MonthId] > MIN(Calendar[MonLongN])))

it doesn't work

 

[Aladin Akyurek]

Where do you have the data -- in a table that one can get with Insert | Table or a pivot table or in an ordinary range?

 

[LearnMeExcel]

Try something like outside the B colum...

=SUM(OFFSET(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)),0,0,-MIN(3,COUNT(B:B))))

thank you Aladin

ths kind of Formula Doesn't work with PowerPivot
Thank again

 

[scottsen]

I might need more info than "it doesn't work" so I can be helpful. But you also put a formula that isn't exactly close to what I said... missing the -4 ?

 

[LearnMeExcel]

please Scott Find my File
https://drive.google.com/open?id=0B-owRCisBn8gbnllN1RUSGxjSHM&authuser=0