Forum Discussion

Albertvw's avatar
Albertvw
Frequent Visitor
10 years ago

Filter latest X months

Have field in TableA with [Month], showing date month (1/month/year) for data. Graphs in report are by Month/Year.

 

How do I then filter a specific card to only show "latest month in TableA's average score".

 

I.e. how can I add a column maybe in TableA where Max[Month] is 0 (not necessarily this month), the month before that -1, etc? Or is there a better way to filter for "latest X months' average"?

 

Thank you

2 Replies

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

    How about a custom column like:

     

    Column = MONTH([Date]) - MONTH(TODAY())

     

    Then, the previous month would be -1.

     

    Would have to be careful about rolling over years and the December/January issue but that's solvable with an if statement or two. 

    • Nhallquist's avatar
      Nhallquist
      Icon for Helper V rankHelper V
      10 years ago

      The way I would do, assuming that this is a Transaction based table, is to use a Flag for Current month.  I would make a column with this formula..

       

      CurrentMonthFlag=IF(Month(TransDate)=Month(Today()), 1,0)

       

      This would create a 1 in the column for every transaction that has occurred "this month".  then, I would filter the object\visual\card for records where CurrentMonthFlag=1.  

       

      Now if the "latest" month is last month, then your formula would look something like: LatestMonthFlag=IF(Month(TransDate)=Month(Today())-1, 1,0)

       

      Greg_Deckler is correct in his caution about allowing other selections and rolling over years.  You would need to test this out thuroughly.  

       

      Something else to think about is what if this month is June 2015, and someone wants to look at data from Feb. 2015.  What should your card display?