Month over Month Comparison on mega table/pivot table

i have some queries hoping if anyone could help. In particularly on Month-over-Month comparison.

So i wanted to create a mega table/pivot table that will show companies


Company |January | Febuary | March | April | Previous Month - Previous Previous Month

A |1 |6 |8 |3|(-5)

B |7 |5 |5 |9|(+4)

C |6 |4 |5 |6|(+1)

D |8 |9 |7 |7|(+0)


For now i would like to make it just previous month comparing with previous previous month. Maybe if possible, i would like to be able to choose the months to compare and make it more dynamic.

So for my question is, i am not sure how to write the calculated field for the beast mode to show this previous month-previous previous month calculation and would like to seek your guidance on this.

Best Answer

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    You can write two beast modes and then join the data back on itself to get previous month and 2 months ago.

    You'll need to format you data in a long format instead of a wide format like you have in your example.

    Something like:

    Company | Month Date | Value
    A | 2023-01-31 | 1
    B | 2023-01-31 | 7
    

    Last Month:

    LAST_DAY(`Month Date` - INTERVAL 1 MONTH)
    

    2 Months Ago

    LAST_DAY(`Month Date` - INTERVAL 2 MONTH)
    


    After you join it based on Last Month -> Month and 2 Months Ago -> Month you can then calculate the different between the two values and then pivot your data back to your desired wide format.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**