You now have until September 15th to submit your use case or success story to the 2022 Dataiku Frontrunner Awards!ENTER YOUR SUBMISSION

End of Month Calculation

Solved!
Alhamdan1995
Level 1
End of Month Calculation

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

0 Kudos
2 Solutions
JordanB
Dataiker
Dataiker

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

View solution in original post

0 Kudos
CatalinaS
Dataiker
Dataiker

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
 

View solution in original post

0 Kudos
10 Replies
JordanB
Dataiker
Dataiker

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

0 Kudos
Alhamdan1995
Level 1
Author

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?

0 Kudos
CatalinaS
Dataiker
Dataiker

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
 
0 Kudos
hemsharma82
Level 1

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

0 Kudos
CoreyS
Community Manager
Community Manager

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?

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as ‘Accepted Solution’ to help others like you!
0 Kudos
hemsharma82
Level 1

@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

0 Kudos

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. 

0 Kudos

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

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

0 Kudos
CoreyS
Community Manager
Community Manager

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

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as ‘Accepted Solution’ to help others like you!
0 Kudos