cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Sum of Previous Month as Calculated Column

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
1 ACCEPTED SOLUTION
Super User

@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])

4 REPLIES 4
Super User

@kevinedora , Create a new column

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

Helper I

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

Super User

@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])

Helper I

@amitchandak it works! Thank you!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors