Calculating Current and Previous Month Sales in Pivot Table

[Purushothaman]

@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!

[Lisa_P]

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.

[Purushothaman]

Hi @Lisa_P ,

Output is incorrect when I followed your approach. .

[Aasir]

In the date load add a Month Year column.
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
Sum({<MonthYear={"$(=Date(MonthStart(AddMonths(Max(SalesDate), -1)), 'MMM-YY'))"}>} Amount)

[Purushothaman]

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

[Dataintellinalytics · Accepted solution]

Hi,

PFA, snapshot.

Please try below expressions

Current Month: Sum(Amount)

Previous Month : Before( Sum(Amount))

[Purushothaman]

@@Dataintellinalytic 
Thanks for the quick help 🙂 Much appreciated.  I am on the subscriber of your YouTube channel. 

[koustubh]

using the max sales date function is finding the max date from the entire set 

and using the add months function is subtraction only 1 month from that maximum sales date, hence we are getting only 2 records when using this formula,