topic Re: Complicated Filtering in DAX Commands and Tips

Complicated Filtering

DominosDave — Thu, 06 Jul 2023 16:22:25 GMT

I have a DAX formula that uses the lastdate availabe depending on what filters the user has in place and looks back 12 months but i need it to ignore any start date filters the user may have placed to always get the full 12 months. Can anyone point me in a direction that could acheive this?

Re: Complicated Filtering

Greg_Deckler — Thu, 06 Jul 2023 17:00:17 GMT

DominosDave You would need to use ALL to bring all of the dates back into context and then filter from there, something along the lines of:

Full 12 Months = VAR __MaxDate = MAX('Dates'[Date]) VAR __EOM12M = EOMONTH(__MaxDate, -12) VAR __MinDate = DATE( YEAR(__EOM12M), MONTH(__EOM12M), 1) VAR __Table = FILTER( ALL('Table'), [Date] >= __MinDate && [Date] <= __MaxDate ) VAR __Result = SUMX( __Table, [Value]) RETURN __Result

Re: Complicated Filtering

DominosDave — Fri, 07 Jul 2023 10:17:21 GMT

Thanks Greg much appreciated!