Issue with Date Time Filter and Functions

[cjaneczko]

I am using the following formula to pull the last 6 months of data starting from the prior month backwards. Essentially I want a rolling 6 months. For some reason though its including data from the first day of this month and does not include data from the first day of July. The data starts on 7/2/2025 and goes through 1/1/2026. I want it to be 7/1/2025 - 12/31/2025. I was using the datetimefirstofmonth() in the formula first and it was giving the same data. I thought maybe it wasnt working properly so i created a column for the first of month using datetimefirstofmonth() to make sure the date is correct. What am I doing wrong and how can I back it out 1 more day.

[budat_mkpf] >= DateTimeAdd([First of Month], -6, "months")
AND 
[budat_mkpf] < [First of Month]

[jrlindem]

If you always need it to be the first of the current month, then back 6 months, I would use DateTimeTrim() like this:

You can combine the formulas into one if you want, but I kept separate to illustrate. For the last day of the interval, you can just subtract 1 day from the First_Day_Current_Month field.

Hope this helps, -Jay

[cjaneczko]

Need it to not include the current month. So it would be the last day of the prior month back 6 months. So 12/31/2025 - 7/1/2025 in this instance.

[jrlindem]

@cjaneczko Using the same concepts though you can just backdate further to the right interval?  I can re-mock if you need, but let me know, -Jay

[cjaneczko]

The formula I've got looks just like the one you posted. But for some reason its offset by a day. Its pulling 7/2/2025 - 1/1/2026. Should be pulling 7/1/2025 - 12/31/2025. 

[cjaneczko]

I had to update the formula to the following in order for it to work. FirstofMonth was not working at all.

[budat_mkpf] >= DateTimeAdd(DateTimeLastOfMonth(), -7, "months")
AND
[budat_mkpf] < DateTimeAdd(DateTimeLastOfMonth(),-1,'month')

[jrlindem]

Right, when you use the following formula:

DateTimeAdd([Date],-6,"months")

If Date = 7/15/2025 for example, taking 6 months away isn't truncating to the first day of the month, it's taking away the months but leaving the days.  So you have to handle that "first of the month" separately.

That's why I started with the: DateTimeTrim([Date], "month")

To get it to truncate to the first day.  Sounds like you got it figured out though?  -Jay