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
01/01/2024	100
02/01/2024	103
03/01/2024	105
04/01/2024	110
05/01/2024	115
06/01/2024	116
07/01/2024	120
08/01/2024	126
09/01/2024	129
10/01/2024	135
11/01/2024	139
12/01/2024	120
01/01/2025	140
02/01/2025	146
03/01/2025	149

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

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

More info here: https://domo-support.domo.com/s/article/360042922814?language=en_US 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)

Previous Month Last Year value Formula

Answers

Categories