March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Hope this helps:
Mange takk!
Hi @sobrien333 ,
Could you please mark the proper answers as solutions?
Best Regards,
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?
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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 |
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |