Previous Month Sales formula

[bdelarosa]

So as each month passes i send out reports to be analyzed. The bosses like the format of 4 columns

YTD Sales vs Last Year Sales

then Previous Month Sales vs Last Year Previous Months Sales. I wish to automate these reports so i never have to touch them just run the schedule.

I have no issues with the 2 columns for YTD Sales vs Last YTD Sales those are pretty straight forward formulas

This issue I come across is the Previous Month Sales Column as this formula works perfect except in January for the new year. Cause in Jan for the new year I want Dec of 2019 sales as my "Previous Month" sales. Any suggestion on how to tweak the below formula to work as I need?

iif([year]=datetimeyear(todate(DateTimeNow())) and [Month]=datetimemonth(datetimeadd(todate(DatetimeNow()),-1,"months")),[CE Beer],0)

[amazz24]

Hi @bdelarosa 

Check out the solution in this post - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Formula-to-always-pull-previous-months...

I think it might solve your issue

[danilang · Accepted solution]

hi @bdelarosa 

Assuming you've got the sales data with a date field available you can use the following formula

IIf([SalesDate]>=DateTimeAdd(DateTimeTrim(DateTimeToday(),"firstofmonth"),-1,"months") and [SalesDate]<=DateTimeAdd(DateTimeTrim(DateTimeToday(),"lastofmonth"),-1,"months"),[CE Beer],0)

This uses [CE Beer] for all the records between the 1st and last days of the previous month and 0 otherwise.

Dan

[bdelarosa]

yes, that makes sense, i was handicapping myself with the current year formula. I wasn't aware 2020-01-01 minus 1 day would take you back to last year, but that makes total sense now.