Month over Month Comparison on mega table/pivot table

Jackie96

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.

DashboardDude

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:

https://youtu.be/-je5d-Q3rH8

John Le

You're only one dashboard away. 

More video solutions at: https://www.dashboarddudes.com/pantry

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.

DashboardDude

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:

https://youtu.be/-je5d-Q3rH8

John Le

You're only one dashboard away. 

More video solutions at: https://www.dashboarddudes.com/pantry