Filtering by month stops working for new year

[afu-iqair]

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 hoping some feedback can get me on the right path to getting this solved.

Here's what the report view looks like:

You can see that the data stops works at the "+3 Month" column, which will be January 2024.

Here's what the query looks like for the "+2 Month" column:

+2 Month = COALESCE( CALCULATE(SUM('Order Lines'[Quantity]), FILTER('Order',YEAR('Order'[Requested Delivery Date])=YEAR('Measures Table'[ESTDate]) && MONTH('Order'[Requested Delivery Date])=MONTH('Measures Table'[ESTDate])+2)),0)

The query that does not work for the "3+ Month" column is:

+3 Month = COALESCE( CALCULATE(SUM('Order Lines'[Quantity]), FILTER('Order',YEAR('Order'[Requested Delivery Date])=YEAR('Measures Table'[ESTDate]) && MONTH('Order'[Requested Delivery Date])=MONTH('Measures Table'[ESTDate])+3)),0)

If I remove the MONTH condition from the FILTER function, I do not get a NULL value, but of course that's not my goal. I did this simply to see where in the entire the query the issue exists.

+3 Month = COALESCE( CALCULATE(SUM('Order Lines'[Quantity]), FILTER('Order',YEAR('Order'[Requested Delivery Date])=YEAR('Measures Table'[ESTDate]))),0)

If I remove the YEAR condition from the FILTER function, and keep the MONTH, the query still returns NULL.

Thank you for any feedback you can provide with this issue.

[littlemojopuppy]

Hi @afu-iqair 

Your data model is incorrect.  With how your data is laid out now, every month you're going to have to adjust what is "current month", "next month" etc.  What you want to have is a table that isn't wide but narrow with date fields for each month joined onto a date table.  You can assign the first/last date of a given month to each value...doesn't matter.

A fundamental principle for good design is the complexity of the code to write measures is inversely proportional to the quality of the data model.  Meaning if you have to write really verbose DAX to calculate basic measures, your design is probably wrong.  Rethink the design of the data model using dates as a field and not as a column and this will probably be much easier.

Just my two cents.  

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file, explain the question and show the expected result.

[MarceloVieira]

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 )

[afu-iqair]

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.

[MarceloVieira]

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

[Erokor]

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. 

[afu-iqair]

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 @MarceloVieira '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)))

[afu-iqair]

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!

[Erokor]

Unfortunately, without having access to the PBIX file, or more screenshots this is going to be a difficult one to help you with. Furthermore, there are dozens of ways to report this information.

From what I can tell is you are persisting filters on the visual that are affecting the "FILTER('Order'..." portion of the DAX.

If there are any visual level filters limiting the years you can see, that would apply before "FILTER('Order'..." unless you specifically remove that context using ALL() (such as "FILTER(ALL('Order')...") however, you would then have to re-institute any outside filters you might want to keep on the 'Order' table.

Another piece might be that your Date table doesn't have enough years/months in it to be able to show or calculate that far out.