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)
Solved! Go to Solution.
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
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
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.