Date Range

[lilianam]

Hello, 

I am trying to create recurring Royalty payment reports to pick up through the 1st day of last month, and the last day of last month. 

When I enter the filter below (today 11/21/19 for ex.):

ToDate([inv_date]) >= DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),-1,"Months"), -1,"Days") AND
ToDate([inv_date]) <= DateTimeAdd(DateTimeAdd(DateTimeLastOfMonth(),-1,"Months"),-1,"Days")

It only picks up data min. 10.03.19 through 10.29.19, even though there is data from 10.01.19 and 10.31.19.

I wonder of the DateTime is what is causing the issue? Any tips?

[Thableaus]

Hi @lilianam 

Yes, you're right.

Add ToDate() to your DateTimeAdd formula and you'll be fine.

Cheers,

[Thableaus · Accepted solution]

@lilianam 

Clarifying.

ToDate([inv_date]) >= ToDate(DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),-1,"Months"), -1,"Days")) AND
ToDate([inv_date]) <= ToDate(DateTimeAdd(DateTimeAdd(DateTimeLastOfMonth(),-1,"Months"),-1,"Days"))

Cheers,

[lilianam]

I needed to change my date to date:time, and after the formula you provided above ran, change it back to just Date using the Select Tool and it works.

Thank you!