We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Finding the Last Day of the Previous Month

BrandonS4
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
AbhilashR
15 - Aurora
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. 

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

AbhilashR
15 - Aurora
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'))

AbhilashR_0-1587164227362.png

 

BrandonS4
8 - Asteroid

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

AbhilashR
15 - Aurora
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.

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

 

BrandonS4_0-1587165418252.png

 

AbhilashR
15 - Aurora
15 - Aurora

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

BrandonS4
8 - Asteroid

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

AbhilashR
15 - Aurora
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')

 

AbhilashR_0-1587166330497.png

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

 

Labels