Finding the Last Day of the Previous Month

[BrandonS4]

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!

[AbhilashR]

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]

@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]

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'))

[BrandonS4]

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

[AbhilashR]

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]

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.

[AbhilashR]

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

[BrandonS4]

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

[AbhilashR · Accepted solution]

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.