Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Finding the Last Day of the Previous Month

8 - Asteroid

Hello all!  I am trying to find the correct formula to find the last day of the previous month.  I am running a report that will calculate DSO and I need to multiply by the number of days in the previous month.  Any help would be appreciated!

Thanks!

11 REPLIES 11
15 - Aurora

Hi @BrandonS4, you could setup your formula in the following way to compute first, last, and last date of previous month for a given date.

``DateTimeAdd(DateTimeTrim([DT],'FirstOfMonth'),-1,'day')``

Also attached is a sample for your reference.

8 - Asteroid

@AbhilashR, this is the formula I currently have:

([Sum_Open AR]/[Sum_A])*(*******)

I need the formula where the stars are at so that each month when I run the data, it will automatically pull in the number of days in the previous month.  Hopefully that makes sense.

15 - Aurora

By number of days are you looking at business days or calendar days? If calendar, then we could use a formula and identify the day element of the last day of the prev. month calculation. Something like this:

``TONUMBER(DateTimeFormat([PREV_MTH_LAST_DT],'%d'))``

8 - Asteroid

Is it possible to do this same function without having a date anywhere in my data?

15 - Aurora

Not sure I understand the question, sorry! Can you give us more context on how your data is structured, or give us a couple rows of anonymized data for us to play with and come up with a skeleton solution for you.

8 - Asteroid

Each month I will have two columns....Total AR and Total Sales.  I want to divide Total AR by Total Sales and then multiply by the number of days in the previous month.  I created a column called DSO and that is where my formula will be.  However, each month will have a different amount of days.  I will always need the previous months number of days.

15 - Aurora

Does your dataset have a date column, or is it always with respect to previous month? i.e in reference to DATETIMETODAY()?

8 - Asteroid

It does not have a date column.  The data will always be in respect to the previous month.

15 - Aurora

Ah, in that case I would replace DT with DateTimeToday(). This way it will always use today as a reference point, and fetch you the last day of previous month.

``````DateTimeAdd(
DateTimeTrim(DateTimeToday(),'FirstOfMonth')
,-1
,'day')``````

Does this work? Sorry it took me some time to understand the ask and how your data is structured.

Labels