Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Filtering by month stops working for new year

I'm a complete novice to Power BI. I have been tasked with figuring out why a query doesn't work for months that land in 2024. I realize that without data this will be hard to figure out, but I'm hop...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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