Announcements
cancel
Showing results for
Did you mean:
Partner - Creator III

## Calculating Current and Previous Month Sales in Pivot Table

@Dataintellinalytics

I am trying to create a pivot table that shows "Current Sales" and "Previous Month Sales" for each "month year"

Data:

 SalesDate Product Amount 10/1/2023 Chair 50 15/1/2023 Fan 100 24/2/2023 Chair 82 23/2/2023 Fan 72 14/3/2023 Chair 132 13/3/2023 Fan 56 10/1/2024 Chair 50 15/1/2024 Fan 100 24/2/2024 Chair 82 23/2/2024 Fan 72 14/3/2024 Chair 132 13/3/2024 Fan 56

Expected outcome in Pivot Table

 Product Jan-23 Feb-23 Mar-23 Jan-24 Feb-24 Mar-24 Current Month Previous Month Current Month Previous Month Current Month Previous Month Current Month Previous Month Current Month Previous Month Current Month Previous Month Chair 50 0 82 50 132 82 50 0 82 50 132 82 Fan 100 0 72 100 56 72 100 0 72 100 56 72

Hope you can help me to solve this requirement 🙂

Thank you very much for your assistance!

Labels (14)

• ### Visualization

1 Solution

Accepted Solutions
Creator

Hi,

PFA, snapshot.

Current Month: Sum(Amount)

Previous Month : Before( Sum(Amount))

6 Replies
Employee

Based on your sample data, with Product and Year-Month dimensions, I calculated the first measure as Sum(Amount) and the second measure as Before(Amount) with Year month moved to column.

Partner - Creator III
Author

Hi @Lisa_P ,

Output is incorrect when I followed your approach. .

Creator III

Date(MonthStart(SalesDate), 'MMM-YY') as MonthYear

Then in the sheet, it's the basics

Current month
Sum({<MonthYear={"\$(=Date(MonthStart(Max(SalesDate)), 'MMM-YY'))"}>} Amount)

Prior Month

Partner - Creator III
Author

Hi @Aasir ,
Need to show all available months on rows and the current and previous values for each. Its still getting incorrect.

Creator

Hi,

PFA, snapshot.