Is there a formula that I can use for the month to month change?

kim_barragan0126

I have the below table, and I would like to add a column for the month-to-month change by amount and %. Is this possible?

ColemenWilson

You can use a lag function.

Final result:

Step 1:
Your data must have some field that orders the data. Month alone won't work, you have to have a numeric or date field. I've added dates to your sample data.

Step 2:
Create a beastmode that is the Previous Value: LAG(Total) OVER (ORDER BY Date)

Step 3:
Create a beastmode to calculate the variance amount: Total - Previous value

Step 4:
Create a beastmode to calculate the variance percentage: (Total - Previous value)/Previous value

You don't need to break these steps out, and can instead have a single beastmode if you prefer. I just wanted to show you step by step.



kim_barragan0126

Thank you! Is the previous month a column I need to add? Or is there a formula etc, that I can use?

ColemenWilson

No you just need the current month. The previous month is derived in the lag function.

However, `Previous Value` is what I named the beastmode in step 2. In steps 3 and 4 I use a nested beastmode that references the beastmode called `Previous Value` created in step 2.

kim_barragan0126

I am getting the below error.

ColemenWilson

That is not an error, only a warning. However, I do see that you need to update the beastmode to use your Total field and your Date field.

Is your data aggregated at the level you first shared? If not, you will need to first aggregate or use a group by.

ArborRose

I think that depends upon whether you use aggregates in your columns.

Month,Dept 1,Dept 2,Dept 3,Dept 4,Dept 5,Total

November,132.0,251.0,59.0,477.0,70.0,989.0

December,127.0,250.0,57.0,366.0,68.0,868.0

January,123.0,235.0,53.0,391.0,68.0,870.0

February,128.0,233.0,58.0,311.0,69.0,799.0

March,124.0,224.0,59.0,401.0,67.0,875.0

April,128.0,222.0,60.0,380.0,64.0,854.0

May,109.0,211.0,49.0,355.0,68.0,792.0

Avg Headcount,124.4,232.3,56.4,383.0,67.7,863.9

ArborRose

lol…sorry about mixing the avg headcount in the months. Obviously, I was straight typing off sample data.