Forum Discussion
Filtering by month stops working for new year
Since those months are measures itself and you know which result you want, cant you just force calculate them??
If you have date slicers, then they will be ignored with this calculate.
measure =
calculate(
sum(result),
removefilters(my date table),
year(date column) = 2023,
month(date column) = 4 )
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.
- Erokor
Resolver II
If 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?
Before my suggestion, try Erokor suggestion.
If you have a month slicer you need some helper columns on the date table and you need to work with:
- yearmonth: 202301, 202302, 202303, ......
- yearmonth_sequential_number: year * 12 + month - 1(which gives you a sequential number)
From there you put on the slicer, "yearmonth" removing the text month, and configuring the slicer to allow only single selections.
After that you probably can calculate yours months like,
measure = var sequencial_year_month = selectedvalue( date_table[yearmonth_sequential_number] ) return calculate( sum(result), removefilters(my date table), date_table[yearmonth_sequential_number] = sequencial_year_month + 3 ) // where you can use +4, +5, +6, +etc for the other measure months
