End of Month Calculation

Alhamdan1995
Alhamdan1995 Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 2 ✭✭✭

I have a date column and I want to show that date as end of month. For example, if one date is 08/01/21, I am looking to show this as 08/31/21. Something similar to End Of Month function in Excel.

Thanks

Best Answers

  • JordanB
    JordanB Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 296 Dataiker
    Answer ✓

    Hi @Alhamdan1995
    ,

    You may want to consider trying the "Increment date" processor within a Prepare recipe. I've included a screenshot below demonstrating how it works. Note that you may make the changes in place or add an output column.

    Screen Shot 2022-07-28 at 4.54.33 PM.png

    You may also find the tutorial on Filtering and Processing Dates helpful!

    If that does not work for you, would you please provide more detailed information on your use case?

    Thanks so much,

    Jordan

  • Catalina
    Catalina Dataiker, Dataiku DSS Core Designer, Registered Posts: 135 Dataiker
    edited July 17 Answer ✓

    Hi @Alhamdan1995
    ,

    You could use a prepare recipe with custom Python functions. An example of a custom Python function that calculates the end of month is below. The input for this is a column called line that contains the dates as Strings (format MM/DD/YYY).

    import datetime
    
    def process(row):
          
        current = datetime.datetime.strptime(row["line"],'%m/%d/%y')
        EndOfMonth = (datetime.datetime(current.year + int(current.month / 12), ((current.month % 12) + 1), 1) - datetime.timedelta(days=1))
        row["line"]= datetime.datetime.strftime(EndOfMonth,"%m/%d/%y")
        return row

Answers

  • Alhamdan1995
    Alhamdan1995 Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 2 ✭✭✭

    Thanks Jordan. This is helpful and will work for what I am looking for now as I have the same date. However, if I have different dates (i.e. 8/1/21 & 8/3/21). If i do the "increment date" here, the first date will be end of month but the second will shift to Sep. What's the solution here?

  • hemsharma82
    hemsharma82 Registered Posts: 4 ✭✭✭

    @Alhamdan1995
    were you able to figure this out? I am also in need of this solution. Please advise. Thanks

  • CoreyS
    CoreyS Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Registered Posts: 1,150 ✭✭✭✭✭✭✭✭✭

    Hi @hemsharma82
    and welcome to the Dataiku Community. Do either of the two accepted solutions here work for your use case? If not can you describe a little bit about how your use case may differ?

  • hemsharma82
    hemsharma82 Registered Posts: 4 ✭✭✭

    @CoreyS
    , thank you for responding. None of the two solutions are working for my use case. My goal is to calculate "Last Day of the Current Month" and "Last Day of the Previous Month". I have use now() function to get the "Current Date" and trying to calculate the above two values from "Current Date Column". I hope this clarifies the requirement. Best, Hem

  • Catalina
    Catalina Dataiker, Dataiku DSS Core Designer, Registered Posts: 135 Dataiker

    Hi @hemsharma82
    ,

    We would like to know why the accepted solutions are not working for your use case. If you are getting any error messages, please share them.

    Can you share also the Python code that you are using? Please confirm the input column type and date format.

  • Catalina
    Catalina Dataiker, Dataiku DSS Core Designer, Registered Posts: 135 Dataiker
    edited July 17

    Hi @hemsharma82
    ,

    Below is the Python code that calculates last day of the current month based on the current date returned by the now() function:

    import datetime
       
    current = datetime.datetime.now()
    print(current)
    
    EndOfMonth = (datetime.datetime(current.year + int(current.month / 12), ((current.month % 12) + 1), 1) - datetime.timedelta(days=1))
    print(EndOfMonth)

    The output of this code is:
    2022-08-04 14:51:54.232576
    2022-08-31 00:00:00
  • hemsharma82
    hemsharma82 Registered Posts: 4 ✭✭✭

    Thank you Catalina. Where exactly in the recipe interface do I write this code?

  • CoreyS
    CoreyS Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Registered Posts: 1,150 ✭✭✭✭✭✭✭✭✭

    Hi @hemsharma82
    as mentioned in @CatalinaS
    original solution, you can use a prepare recipe with custom Python functions. I hope this helps!

  • Ian
    Ian Dataiku DSS Core Designer, Registered Posts: 1

    Hi @CatalinaS
    ,

    Granted, my knowledge of Python is very limited. I have tried to use this code in a prepare recipe (both with and without the "def process(row):" and "return len(row)" that are inserted automatically) and keep getting errors.Screenshot 2022-10-14 102044.jpgScreenshot 2022-10-14 103513.jpg

  • chi_wong
    chi_wong Registered Posts: 5 ✭✭✭✭

    if you don't want to use python, you can do it in a formula step

    inc(concat(datePart(audt_start_dt_max,"year"),"-",mod(datePart(audt_start_dt_max,"Month"),12)+1,"-01"),-1,"day")

  • PrathameshPatil
    PrathameshPatil Dataiku DSS Core Designer, Registered Posts: 2

    This formula works perfectly until we are looking at December. A minor modification to to include December:
    if(
    datePart(audt_start_dt_max,"Month") == 12 ,
    inc(concat(datePart(audt_start_dt_max,"year")+1,"-",mod(datePart(audt_start_dt_max,"Month"),12)+1,"-01"),-1,"day"),
    inc(concat(datePart(audt_start_dt_max,"year"),"-",mod(datePart(audt_start_dt_max,"Month"),12)+1,"-01"),-1,"day")
    )

  • GabrielBerno
    GabrielBerno Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 3

    This formula brings the last day of the month, is there a way that we could get the last working/business day of the month?

  • Catalina
    Catalina Dataiker, Dataiku DSS Core Designer, Registered Posts: 135 Dataiker
    edited July 17

    Hi @GabrielBerno
    ,

    You can get the last working day of the month using this custom Python function:

    import datetime
    from datetime import timedelta
    
    def process(row):
        current = datetime.datetime.strptime(row["line"],'%m/%d/%y')
        EndOfMonth = (datetime.datetime(current.year + int(current.month / 12), ((current.month % 12) + 1), 1) - datetime.timedelta(days=1))
        if EndOfMonth.isoweekday() >5:
            EndOfMonth = EndOfMonth - datetime.timedelta(days=EndOfMonth.isoweekday()-5)
        row["EndWeek"]= datetime.datetime.strftime(EndOfMonth,"%m/%d/%y")
        return row

  • GabrielBerno
    GabrielBerno Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 3

    @CatalinaS
    thanks very much, that works perfectly for the weekends but, besides the weekends, I would like to consider the holidays too. I tried the "Flag holidays" processor but it doesn't includes brazilian holidays, do you have any ideas that I could adopt?

    PS: I'm from Brazil.

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,601 Neuron

    @GabrielBerno
    ,

    if you are comfortable with python recipes you might try using the python library https://pypi.org/project/holidays/

    or if you can find or create a table of Brazilian holidays you could join this to your dataset.

    just a couple of ideas. Let us know how you get on with this.

  • GabrielBerno
    GabrielBerno Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 3

    @tgb417
    Thank you! I guess I will go with the table of Brazilian holidays option.

Setup Info
    Tags
      Help me…