- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@kevinedora , Create a new column
sumx(filter(Table, Table[Acct] =earlier([Acct]) && [Item] = earlier([Item]) && eomonth([date],0) =_date) , [Sales])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@amitchandak It's not working. It's all blank. Maybe I've done something wrong? Please see screenshot below
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
05-13-2024 06:38 AM | |||
09-25-2024 05:59 AM | |||
05-02-2024 11:36 AM | |||
05-01-2024 01:55 PM | |||
03-10-2024 05:48 PM |
User | Count |
---|---|
137 | |
107 | |
84 | |
59 | |
46 |