# 3 month moving with current month for a date column

I am trying to calculate a SQL formula for a moving 90 day period based on month. For example what I would like to see now is the date column rolled up into DEC,JAN,FEB and as we move to next month I would expect to see JAN,FEB,MAR.

I know the current month function to get only the current month moving but having trouble integrating the following two months as well with it.

Thanks

Tagged:

• @gbrown You will need to use a Window function for this calculation, although those functions are not supported in Domo's version of MySQL. You would have to use either the Rank & Window ETL tile or write it in a beast mode.

• @MichelleH @MarkSnodgrass How would I go about this in a window function under the rank and window tile? or do you know what the beastmode would look like?

• @gbrown Here is the knowledge base article on how to configure the Rank & Window tile in ETL: https://domo-support.domo.com/s/article/360044876094?language=en_US

For your situation, you would want to use a framed average or sum, ordered by Date. To calculate the 3-month average you would set Preceding = 0 (start with current month) and Following = 2 (calculate through two months from now).

• @MichelleH Is there a way to do a filter formula to calculate without the rank and window tile. it can be in a beastmode if needed. Something similar to

CASE WHEN YEAR(`Date`) >= YEAR(CURDATE()) AND MONTH(`Date`) <= ADDDATE(CURDATE(), INTERVAL 3 MONTH)

THEN 'SHOW'

ELSE 'HIDE'

END

The one problem is that it is picking up end months for 2023 and start for 2024 ( I am not sure why) but it seems like it could work maybe my logic needs help.

Thanks

• @gbrown A beast mode filter would work, with a couple changes:

CASE `Date` <= DATE_ADD(CURDATE(), INTERVAL 2 MONTH) and `Date` > DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

THEN 'SHOW'

ELSE 'HIDE'

END

• @gbrown if you are only wanting to show the current month and the next two months and wanting to show the average of those values, I think you could keep it pretty simple by using the last_day function and the date_add function like this:

```CASE when LAST_DAY(`dt`) >= LAST_DAY(CURRENT_DATE()) AND LAST_DAY(`dt`) <= LAST_DAY(DATE_ADD(CURRENT_DATE(), INTERVAL 3 month))
THEN 'Inside 90-day window'
ELSE 'Outside 90-day window'
END
```

You would then add this to your filter and filter to "Inside 90-day window" and then drag your value field into your table and choose Average for the aggregation type.

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.