Forum Discussion

Cbutler's avatar
Cbutler
Icon for Helper III rankHelper III
4 years ago

Calculated table filter by N days

Help!  I am trying to create a calculated table that filters by a dynamic date to pull back the results for the last n or 10 days.  So I need a calculated table that is written to show the MAX ...
DAX
Filter
summerize
Table
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

Cbutler can you try this

Filter (tbl1, tbl1[date]=calculate(max(tbl2[date]),all(tbl2)))

  • Cbutler's avatar
    Cbutler
    Icon for Helper III rankHelper III
    4 years ago

    smpa01 Thanks for replying..... Where do I add in the -10 days? 

    Its bringing back all the days still... I just need the last -10 days from the as at date filter 

     

    this is how I have done it in the measure that works, but I need the table version for other calcs 

    Greg_Deckler any ideas? 🙂 

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Cbutler If you are trying to create a new table, maybe use CALCULATETABLE? Not sure I'm fully in sync with what you are trying to do. But something like:

      Table = 
  VAR Last_Date = MAX('Date'[Date])
RETURN
  CALCULATETABLE('Table',FILTER(ALL('Date'[Date]),'Date'[Date]>Last_Date - 10 && 'Date'[Date]<=Last_Date))
      • Cbutler's avatar
        Cbutler
        Icon for Helper III rankHelper III
        4 years ago

        Greg_Deckler oh thats got me closer, but the as at date is not changing the table filter. 
        Its filtering from MAX table date (today) -10 so pulling data form 15-24th (no data in there for today), but when I change my filter as below...I need the table to recalculte the -10 days, so selecting 15/10/21 would filter the table to show 6/10/21 to 15/10/21...