Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
@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!
Hi,
PFA, snapshot.
Please try below expressions
Current Month: Sum(Amount)
Previous Month : Before( Sum(Amount))
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.
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)
Hi @Aasir ,
Need to show all available months on rows and the current and previous values for each. Its still getting incorrect.
Hi,
PFA, snapshot.
Please try below expressions
Current Month: Sum(Amount)
Previous Month : Before( Sum(Amount))
@@Dataintellinalytic
Thanks for the quick help 🙂 Much appreciated. I am on the subscriber of your YouTube channel.
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,