Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sobrien333
Regular 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.

11 REPLIES 11
Padycosmos
Solution Sage
Solution Sage

Hope this helps:

Padycosmos_1-1678564559976.png

 

 

Håkon
Advocate II
Advocate II

Mange takk!

v-jiascu-msft
Microsoft Employee
Microsoft Employee

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.
sobrien333
Regular Visitor

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?

Hello,

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

Regards,
ElenaN

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.

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors