Reply
kevinedora
Helper I
Helper I
Partially syndicated - Outbound

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.

 

DateAcctItemSalesPrevMonthSales
1/01/2022123456A10000 
1/01/2022123456B15000 
2/01/2022123456A2000010000
2/01/2022123456B1800015000
1 ACCEPTED SOLUTION

Syndicated - Outbound

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

Syndicated - Outbound

@kevinedora , Create a new column

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

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

 

kevinedora_0-1661318749435.png

 

Syndicated - Outbound

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

@amitchandak it works! Thank you!

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)