Sum of Previous Month as Calculated Column

[kevinedora]

Hi,

I'm trying to create a previous month's sales as a calculated column, I've created this in Measure easily however I don't know how to do this as Calc Column. Can someone help? Thank you!

Criteria: Acct and Item should be the same
Note: I have 5 Million rows, when I try to do some testing calc it crashes my PBI. It would help if the calculation is friendly to large data sets.

Date	Acct	Item	Sales	PrevMonthSales
1/01/2022	123456	A	10000
1/01/2022	123456	B	15000
2/01/2022	123456	A	20000	10000
2/01/2022	123456	B	18000	15000

[amitchandak]

@kevinedora , Create a new column

sumx(filter(Table, Table[Acct] =earlier([Acct]) && [Item] = earlier([Item]) && eomonth([date],0) =_date) , [Sales])

[kevinedora]

@amitchandak It's not working. It's all blank. Maybe I've done something wrong? Please see screenshot below

[amitchandak · Accepted solution]

@kevinedora , oh you do not have date,

First create period rank column

period Rank = rankx(Table, [period],,asc, dense)

then create a new column

sumx(filter(Table, Table[Acct] =earlier([Acct]) && [Item] = earlier([Item]) && [period Rank ]=earlier([period Rank]) -1 ) , [Sales])

[kevinedora]

@amitchandak it works! Thank you!