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


  • GrantSmith

    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!**
  • DashboardDude
    Answer ✓

    Hi @Jackie96 ,

    I got this question earlier today so I guess it was you! Here's a video for everyone else to see how I approached this problem:

    John Le

    You're only one dashboard away. 

    More video solutions at:

    John Le

    You're only one dashboard away.

    Click here for more video solutions: