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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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