Previous Month Last Year value Formula

_DanielB_ · March 27

Hi Everyone

I'm trying to calculate a sum of values for the previous month for the previous year (February 2024 in this case) so that I can compare the difference to previous month for this year

I've made a few attempts but have had no success

What is the best way to do this calculation for last year previous month?

Thanks

Dan

ColemenWilson · March 27

Hi @_DanielB_ , I would recommend using one of Domos period over period charts:

_DanielB_ · March 27

Hi @ColemenWilson

I need to create the formula in the ETL as I have to include it in other calculations in my output dataset

I tried the below but that didn't return any values and there are definitely values for last year

CAST(

CASE WHEN YEAR(`Month`) = YEAR(CURDATE()) -1 AND MONTH(`Month`) = MONTH(CURDATE()) -1 THEN

CAST(
CASE
WHEN Cost_Type IN ('Profit', 'Costs') AND CTYPE IN ('EXP', 'INC') THEN
Cost (£)
ELSE
'0'
END
AS DECIMAL
)
ELSE
0
END
AS DECIMAL
)

ColemenWilson · March 27

Gotcha. I would recommend using a LAG function

Structure your data to look something like this:

Month	Amount
Month	01/01/2024	Amount	100
Month	02/01/2024	Amount	103
Month	03/01/2024	Amount	105
Month	04/01/2024	Amount	110
Month	05/01/2024	Amount	115
Month	06/01/2024	Amount	116
Month	07/01/2024	Amount	120
Month	08/01/2024	Amount	126
Month	09/01/2024	Amount	129
Month	10/01/2024	Amount	135
Month	11/01/2024	Amount	139
Month	12/01/2024	Amount	120
Month	01/01/2025	Amount	140
Month	02/01/2025	Amount	146
Month	03/01/2025	Amount	149

2. Use LAG to get the previous year value as a new field, `1YearPreviousAmount`

Month	Amount	1YearPreviousAmount
Month	01/01/2024	Amount	100	1YearPreviousAmount
Month	02/01/2024	Amount	103	1YearPreviousAmount
Month	03/01/2024	Amount	~~105~~	1YearPreviousAmount
Month	04/01/2024	Amount	110	1YearPreviousAmount
Month	05/01/2024	Amount	115	1YearPreviousAmount
Month	06/01/2024	Amount	116	1YearPreviousAmount
Month	07/01/2024	Amount	120	1YearPreviousAmount
Month	08/01/2024	Amount	126	1YearPreviousAmount
Month	09/01/2024	Amount	129	1YearPreviousAmount
Month	10/01/2024	Amount	135	1YearPreviousAmount
Month	11/01/2024	Amount	139	1YearPreviousAmount
Month	12/01/2024	Amount	120	1YearPreviousAmount
Month	01/01/2025	Amount	140	1YearPreviousAmount	100
Month	02/01/2025	Amount	146	1YearPreviousAmount	103
Month	03/01/2025	Amount	149	1YearPreviousAmount	~~105~~

More info here: Site favicon Customer Support Community

scroll down to the "Offset Functions" section

_DanielB_ · March 27

Thanks @ColemenWilson I'll give that a try :)

Manasi_Panov · March 27

@_DanielB_

This should work. I'm using it on many occasions:

Value Previous Month:

SUM(CASE
   WHEN YEAR(CURRENT_DATE()) = YEAR(`SALES_DATE`) AND MONTH(CURRENT_DATE()) - 1 = MONTH(`SALES_DATE`) THEN `SALES_AMOUNT`
   ELSE 0
   END)

Value Last Fiscal Year Same Month:

SUM(CASE
   WHEN YEAR(CURRENT_DATE())-1 = YEAR(`SALES_DATE`) AND MONTH(CURRENT_DATE()) - 1 = MONTH(`SALES_DATE`) THEN `SALES_AMOUNT_HC`
   ELSE 0
   END)

Magic ETL

Magic ETL

Previous Month Last Year value Formula

Welcome!

Answers

Welcome!

Welcome!

Quick Links

Categories