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

[Pilsner]

Hello @cjaneczko,

I've taken a look at your formula, and I do agree that it initially looks like it should work. 

Having taken a closer look, I believe that the issue lies within the data types. Your column [First of Month] and the formula DateTimeAdd([First of Month], -6, "months"), both result in "DateTime" data types, whereas your [budat_mkpf] column is a Date data type. This appears to mean that they behave differently and actually have a hierarchy / ordering between them. I did some testing and found the following:

This was the input data I used for the tests:

1) Initially looking at the data my assumption was that 2026-01-02 = 2026-01-02 00:00:00 however that turned out not to be the case.

2) This first result suggested that the date / date time, must have a hierarchy. I filtered to values where the date time was less than the date:

3) And then to values where the date was less than the date time:

This then seemed to confirm my theory. It appears as if the date is treated as coming before ALL date time values on that given day, even when the time is 00:00:00. 

With this new understanding of the date vs date time ranking in mind, I can now see why your initial formula didn't quite behave as expected. The easiest way I found to get around this was to simply convert your Date time values, into dates. You can do this by wrapping the expressions in a ToDate() function, from within your filters formula:

Appreciate this may seem a little strange at first so I have attached the workflow containing both my testing and solution below. I hope this all makes sense but please let me know if you have any questions. 

Regards - Pilsner