Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
kevinedora
Helper I
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.

 

DateAcctItemSalesPrevMonthSales
1/01/2022123456A10000 
1/01/2022123456B15000 
2/01/2022123456A2000010000
2/01/2022123456B1800015000
1 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])

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@kevinedora , Create a new column

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

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

 

kevinedora_0-1661318749435.png

 

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

@amitchandak it works! Thank you!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.