Sales report - Today minus 1 day

[bcwrigh3]

Hi everyone,

I have written a report that pulls daily sales.  The report is currently set to pull the current month based on a posting date.  This works great except for the first day of the new month.  With this logic I am missing the last day of sales for the month.  On the first day of a new month, I need to pull the previous month's data, effectively giving me all of the data for the previous month.  Is there a formula I can use that essentially says take today's date minus 1 day and give me the data for the month that day (today -1 day) equals?

[bcwrigh3]

Hi Pete,

Thanks for the response.  I'm sure i'm showing my noobness here, but would this be entered in the Advanced Editor?  I've attached what my query looks like as of now.

[BA_Pete · Accepted solution]

@bcwrigh3 ,

No worries. The easiest way to do it would be to filter [yourDateField] and just select one value - any value. This will prepare a query step with most of the code you need, like this:

Then you can just replace everything after the 'each ' and before the final ')' with the code I gave you, obviously swapping 'yourDateField' with the real name of your posting date field.

So, for my example, it would look like this:

*NOTE* I have updated the code in my original post as I missed out two full stops.

Let me know how you get on.

Pete

[bcwrigh3]

Pete,

I greatly appreciate your time and advice.  This appears to be working perfectly.  Thanks!

[BA_Pete]

Hi @bcwrigh3 ,

Try this as a table filter:

if Date.Day(DateTime.LocalNow()) = 1
then Date.IsInPreviousMonth([yourDateField])
else Date.IsInCurrentMonth([yourDateField])

Pete