Sum Last X months of data

[sobrien333]

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.

[Padycosmos]

Hope this helps:

[Håkon]

Mange takk!

[v-jiascu-msft]

Hi @sobrien333 ,

Could you please mark the proper answers as solutions?

Best Regards,

[sobrien333]

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]

Hello,

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

Regards,
ElenaN

[sobrien333]

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.

[MFelix]

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

[sobrien333]

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

[Ashish_Mathur]

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.

[MFelix]

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

[MFelix]

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