Get start of the year based on the year of the previous month of a date

[Ruchita]

Hi everyone,

I'm looking to write a formula such that the startdate of the year (Jan 1st) is obtained based on the year of the previous month.

For example :

if the input is "2022-01-01" then the formula should return "2021-01-01" (Since year of previous month would be 2021)

if the input is "2022-04-01" then the formula should return "2022-01-01" (since the year of previous month would be 2022)

Thanks in advance !

[shashidhar_sris · Accepted solution]

Hi Ruchita,

You can use below formula to get your desired result.

==============================================

case(

    month(

        Shashi_Test.Sample_Store.Ship_Date

    ) = 1, 

    monthStartDate(

        addYears(

            Shashi_Test.Sample_Store.Ship_Date, 

            -1

        )

    ), 

    monthStartDate(

        addMonths(

            Shashi_Test.Sample_Store.Ship_Date, 

            1 - (month(

                Shashi_Test.Sample_Store.Ship_Date

            ))

        )

    )

)

========================================

Please let us know if you need more details.

Regards,

Shashidhar.S

[Ruchita · Accepted solution]

Hi Shashidhar,

Thank you for your prompt reply. I also found the below formula to work just fine.

date(

                    concat(

                        string(

                            year(

                                addMonths(

                                    date(

                                        ABC.DateDimension.Date

                                    ), 

                                    -1

                                )

                            )

                        ), 

                        "-01", 

                        "-01"

                    )

Thank you once again for your reply.

[anurag]

This formula should also work:

date(

     dateTrunc(

     addMonths( sales.date.order_date,-1),

      'year')

)