Date expression query

[Zub]

I have the following expression hardcoded for month of september:

= (DueDate >= Date('01/09/2024') AND DueDate <= Date('30/09/2024'))

Is there a way to make the date part dynamic so that for each month I don't have to keep changing it manually? Like for example for next month (october) I will have to manually change the expression to:

= (DueDate >= Date('01/10/2024') AND DueDate <= Date('31/10/2024'))

Thanks for any help

[Kushal_Chawda]

@Zub  you can do it using today()  with monthstart() and monthend()

(DueDate >= monthstart(today()) AND DueDate <=monthend(today()))

[Zub]

Hi @Kushal_Chawda . Many thanks for your reply. That works very well. Just another thing I had not realised is that I'd like to do this at the start of each month for the previous months data. So the expression would need to be for the previous months start and end days.... the monthstart and monthend would have to be for the previous month. How could i tweak the expression you gave to do this? Many thanks for your help

[Kushal_Chawda · Accepted solution]

@Zub  you can tweak it as below

(DueDate >= monthstart(addmonths(today(),-1)) AND DueDate <=monthend(addmonths(today(),-1)))

[Zub]

@Kushal_Chawda That works. Thx!