Forum Discussion

DominosDave's avatar
DominosDave
Regular Visitor
3 years ago
Solved

Complicated Filtering

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 p...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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's avatar
DominosDave
Regular Visitor
3 years ago

Thanks Greg much appreciated!