Complicated Filtering

[DominosDave]

Hi

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?

[Greg_Deckler · Accepted solution]

@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

[DominosDave]

Thanks Greg much appreciated!