Between _add_months configurastion

Question

I have a report in which I'm using a filter on vehicle Wholesale date (calendar measure) trying to bring in the previous months date of transactions.

For example: Today is 8/10/23. I am wanting to see all data points that processed throughout the previous month (July 1st through July 31) (First day of month through last day of month).

I've used variations of "between (_add_days(_first_of_month(_add_months(current_date, -1))) and (_first_of_month(current_date)))" and "between _first_of_month (_add_months (current_date,-1) and _last_of_month (_add_months (current_date)".

When I use "(_first_of_month(_add_months(current_date, -1)))" only data from 7/1 show with nothing from 7/2 on. What am I missing?

Answer 1

I've used variations of "between (_add_days(_first_of_month(_add_months(current_date, -1))) and (_first_of_month(current_date)))" and "between _first_of_month (_add_months (current_date,-1) and _last_of_month (_add_months (current_date)".

Ok what results did you get? What did the generated SQL look like? The second of your examples should work but I'm guessing what you actually did and what you say you did.

When I use "(_first_of_month(_add_months(current_date, -1)))" only data from 7/1 show with nothing from 7/2 on. What am I missing?

What results did you get? What did the SQL look like? What is the complete filter expression are you using? That, like the other two examples you used, are only fragments.

I believe you want to filter your report so that only the records to only have those for some activity between the first of the month of the previous month and the last of the month of the previous month. Is that correct understanding?

If so create a filter similar to this.

wholesale date between _first_of_month ( _add_months ( current_date,-1) ) and _last_of_month ( _add_months ( current_date,-1) )

wholesale date would be the object reference (the stuff with square brackets if you are using a FM package (data module identifiers use the query subject (aka table).query item (aka column) syntax)) to the column.

The fact that you say you have tried something similar to that and it did not work (although you do not say what happened) means that you are not stating all the relevant details.

Answer 2

So, after much tinkering with the suggestions above and others, was finally able to add the filter from the Query Detail Filters (Report->Queries) as [Date Wholesaled] between _first_of_month (_add_months (current_date, -1)) and _last_of_month (_add_months (current_date, -1))

I had to add the filter using the "Detail Filter Expression" as opposed to the Filters-Query. Pics are below: