Sum by current and the last two months

[Anonymous]

Hello community 

 I want to sum the last 3 months for each Month by client 

This exemple of data 

Month	Client	Amount
Decembre	A	5
Jan	A	2
Feb	A	3
March	A	4
April	A	5

Wanted Result 

Month	Client	Amount
Decembre	A	5
Jan	A	7
Feb	A	(3+2+5) = 10
March	A	(4+3+2) = 9
April	A	5 +4+3 = 12

I want to sum the amount of the current month and the last two months for each  customer as calculated column not a measure

Best Regards and Thank You for your help

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Not sure whether your data is that simple as your example.

Try below method:

Create an index column in query editor;

Then create a calculated column as below:

Column = 
CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Index]>=EARLIER('Table'[Index])-2&&'Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Client]=EARLIER('Table'[Client])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[DataZoe]

@Anonymous To do this as a calculated column, I first figured out a proper date format for your month:

Month Date = 
VAR First3 =
    LEFT ( [Month], 3 )
VAR SearchString = "XXJanFebMarAprMayJunJulAugSepOctNovDec"
VAR MonthNum =
    DIVIDE ( SEARCH ( First3, SearchString, 1, -1 ), 3 )
VAR YearNum =
    IF ( MonthNum <= 4, 2021, 2020 )
RETURN
    DATE ( YearNum, MonthNum, 1 )

Then the requested calculated column:

Amount Plus Last 2 =
VAR client = 'Table'[Client]
VAR maxmonth = 'Table'[Month Date]
VAR minmonth =
    DATE ( YEAR ( maxmonth ), MONTH ( maxmonth ) - 2, 1 )
VAR newamount =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        ALL ( 'Table' ),
        'Table'[Month Date] >= minmonth
            && 'Table'[Month Date] <= maxmonth
            && 'Table'[Client] = client
    )
RETURN
    newamount

You can also create the measure then reference it in a calculated column too.