cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sobrien333 Frequent Visitor
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
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 )
    )
)

 

Again without additional information is difficult ot give you an answer.

 

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sobrien333 Frequent Visitor
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?

ElenaN Regular Visitor
Regular Visitor

Re: Sum Last X months of data

Hello,

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

Regards,
ElenaN
sobrien333 Frequent Visitor
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
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sobrien333 Frequent Visitor
Frequent Visitor

Re: Sum Last X months of data

Hi MFelix. Yes, see sample below:

Customer_IDTransaction_Date Transaction_Amount Store
123451/1/2019 $                              115.001
678901/4/2019 $                                94.002
234562/9/2019 $                                37.002
789013/1/2019 $                                85.002
123451/28/2019 $                              202.001
678902/17/2019 $                              114.001
234562/22/2019 $                                74.002
789013/7/2019 $                                68.001

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

DateDay of WeekMonthYearDay of MonthCalendar MonthCalendar Quarter
1/1/2011SaturdayJanuary2011111
1/2/2011SundayJanuary2011211
1/3/2011MondayJanuary2011311
1/4/2011TuesdayJanuary2011411
1/5/2011WednesdayJanuary2011511
1/6/2011ThursdayJanuary2011611
1/7/2011FridayJanuary2011711
1/8/2011SaturdayJanuary2011811
1/9/2011SundayJanuary2011911
Super User
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] )
    )
)

Check the link to this article where you have additional explanation on how it works.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
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
Community Support Team

Re: Sum Last X months of data

Hi @sobrien333 ,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.