Filter only current month data

[Saravanan13]

Hello team,

I am looking to filter current month data from a data set. The data set will always have data till previous. (current date - 1day). I need to report data till yesterday.

I used  DateTimeFormat([Status Start Date], "%Y-%m") = DateTimeFormat(DateTimeToday(), "%Y-%m") as a filter, Its working fine but its not working during start of the month, Since the month does not match I am getting blank values.

Can anyone assist on this.

Sample data

Stage start date	A	B	C
10/26/2023	6	8	6
10/27/2023	6	8	6
10/28/2023	6	8	6
10/29/2023	6	8	6
11/26/2023	11	8	6
11/27/2023	4	8	2
11/28/2023	6	8	6
11/29/2023	5	8	6

[Qiu]

@Saravanan13 
Say today is 2023-12-01, then your date will be upto "2023-11-30", since you want to filter the current Month "December" so there is no surprise you will get nothing.
What do you expect the result for today "2023-12-01"?

[Hammad_Rashid]

Check if this works:

[Stage start date] >= DateTimeAdd(DateTimeFirstOfMonth(DateTimeToday()), -1) AND [Stage start date] <= DateTimeAdd(DateTimeToday(), -1)

[Saravanan13]

On 1st December I should get November month data and starting from 2nd December I should get December data.

[caltang]

I would caution against DateTimeNow() if you want it to be flexible. Rather, I would advise to use an Analytic app where you can select the dates to give you ultimate flexibility. 

[Saravanan13]

I am have not used Analytic app, do you have any sample workflow

[Saravanan13]

There is some issue with the syntax, can you provide the formula along with the workflow.

[Saravanan13]

On 1st December I should get November month data and starting from 2nd December I should get December data.

[CoG · Accepted solution]

If the 1st of each month should always return the prior month, you can use an adjust formula:

DateTimeFormat([Status Start Date], "%Y-%m") = DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"day"), "%Y-%m")

This would push back dates considered by one day, thus giving you the desired result.