Forum Discussion
Filtering by month stops working for new year
Thanks for your feedback. This does work, but if I created it like this I would have to update the month values once a month. I tried using MONTH(TODAY())+3 to get a date 3 months from now, but it returns 13, as expected and that causes the FILTER function to value since the months from my table data are 1-12.
Resolver IIIf you are looking for relative filtering based on the current month you should add the months to your today function by way of the EDATE Function:
EDATE(TODAY(),4) This adds the number of months to a current date given.
Thank you for the feedback. That's interesting. When I look at the value of EDATE it returns something like this: 2/13/2024 12:00:00 AM. However, MONTH('Order'[Requested Delivery Date]) in the FILTER function is returning a string representation of the month, like "DECEMBER". Would I have to do some kind of conversion to one of they values to use the "=" comparison operator?
- Erokor
You'll want to ensure your 'Order'[Requested Delivery Date] is of "Date" or "DateTime" data type. In addition with Anonymous 's comment, there is an intersection that happens and overwrites itself. Meaning if you were to want to do both you're better off doing something like CALCULATE([SomeCalculation],FILTER(ALL('mydatetable'), MONTH(date you want to use) && YEAR(date you want to use)))
I was able to use EDATE(TODAY(),4) after all. I hadn't realized I could wrap it inside of MONTH() to get the number of the month. Doh! Thanks for all of the feedback in this thread!