cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum Last X months of data

I have a dataset that tracks customer purchases and I'm trying to calculate total spend over the last 12 months. I've tried numerous formulas as 'Measures' but to no avail. Each Customer ID can and does appear multiple times, along with dates. My data table has a date field but I've also created and linked to it a Calendar table that has dates along with a number of other calendar type fields (day of week, week of year, etc). Anyone have any suggestions? Thanks.

9 REPLIES 9
Super User

## Re: Sum Last X months of data

Hi @sobrien333 ,

Without any specific information it's difficult to give you an answer but you should try something like this:

```Last 12 Months =
CALCULATE (
SUM ( Table[Column] );
FILTER (
ALL ( Calendar[Date] );
Calendar[Date] <= MAX ( Calendar[Date] )
&& Calendar[Date] >= DATEADD ( Calendar[Date]; -12; MONTH )
)
)```

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: Sum Last X months of data

Thanks MFelix. I copied/pasted that and got an error saying: The syntax for ';' is incorrect. I did swap out Table[Column] for the table and column I'm using. Calendar[Date] is exactly what I call my calendar and date fields, so I didn't change those. Any idea what might be wrong?

Regular Visitor

## Re: Sum Last X months of data

Hello,

Try using ',' wherever you have ';' in the formula provided by MFelix.

Regards,
ElenaN
Frequent Visitor

## Re: Sum Last X months of data

Thanks ElenaN. I did that and that produced a working result, but its going beyond 12 months and adding the total that is in the table, which goes back several years.

Super User

## Re: Sum Last X months of data

Hi @sobrien333 ,

As refered without any addtional information about your data is difficult to really give you the best formula, can you share a sample of your file or a mockup of your data?

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: Sum Last X months of data

Hi MFelix. Yes, see sample below:

 Customer_ID Transaction_Date Transaction_Amount Store 12345 1/1/2019 \$                              115.00 1 67890 1/4/2019 \$                                94.00 2 23456 2/9/2019 \$                                37.00 2 78901 3/1/2019 \$                                85.00 2 12345 1/28/2019 \$                              202.00 1 67890 2/17/2019 \$                              114.00 1 23456 2/22/2019 \$                                74.00 2 78901 3/7/2019 \$                                68.00 1

And then I have a calendar file that looks like this. And I have a relationship setup between Date and Transaction_Date.

 Date Day of Week Month Year Day of Month Calendar Month Calendar Quarter 1/1/2011 Saturday January 2011 1 1 1 1/2/2011 Sunday January 2011 2 1 1 1/3/2011 Monday January 2011 3 1 1 1/4/2011 Tuesday January 2011 4 1 1 1/5/2011 Wednesday January 2011 5 1 1 1/6/2011 Thursday January 2011 6 1 1 1/7/2011 Friday January 2011 7 1 1 1/8/2011 Saturday January 2011 8 1 1 1/9/2011 Sunday January 2011 9 1 1
Super User

## Re: Sum Last X months of data

Hi @sobrien333 ,

I tried to make a mockup of your data and used tihs new measure:

```Last 12 Months =

CALCULATE (
SUM ( Table1[ Transaction_Amount ] );
DATESBETWEEN (
DateTable[Date];
NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( DateTable[Date] ) ) );
LASTDATE ( DateTable[Date] )
)
)```

Regards,

MFelix

Proud to be a Datanaut!

Super User

## Re: Sum Last X months of data

Hi,

In your visual drag Year and Month from the Calendar Table.  Write and drag this measure

=CALCULATE(SUM(Data[Transaction_Amount]),DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-11),MAX(Calendar[Date])))

Hope this helps.

Community Support Team

Hi @sobrien333 ,