Previous Month Last Year value Formula

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

Answers

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

    If I solved your problem, please select "yes" above

  • 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
    )

  • Gotcha. I would recommend using a LAG function

    1. 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

    If I solved your problem, please select "yes" above

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

  • Manasi_Panov
    Manasi_Panov Contributor

    @_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)

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.