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 placed to always get the full 12 months. Can anyone point me in a direction that could acheive this?

 

 

  • 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

2 Replies

  • 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