Forum Discussion

BuzzwordMaster's avatar
BuzzwordMaster
Regular Visitor
9 years ago
Solved

Show values for items NOT in current month

Hi all,   I'm kicking myself over this one... it is Monday here after all.   I have items that include a date value, number value, title (text), and a status (text). I currently have a chart setu...
  • erik_tarnvik's avatar
    erik_tarnvik
    9 years ago

    You can either use "advanced filtering" instead of relative dates (maybe obvious) but then you would have to select the specifc date you want for the cut-off rather than a relative date.

     

    The other option is to introduce a slicer on dates into your view. 

erik_tarnvik's avatar
erik_tarnvik
Solution Specialist
9 years ago

You can either use "advanced filtering" instead of relative dates (maybe obvious) but then you would have to select the specifc date you want for the cut-off rather than a relative date.

 

The other option is to introduce a slicer on dates into your view. 

BuzzwordMaster's avatar
BuzzwordMaster
Regular Visitor
9 years ago

Thanks for the reply Erik. I ended up going with your second option and added a custom column for 'IsCurrentMonth'. I completely forgot about that option this morning and needed something that would roll over month to month. For those interested:

 

IsCurrentMonth = IF(MONTH('Table Name'[Date])=Month(NOW()), "Current Month", "Other")

 

Sliced it using the 'Other' option and was good to go.

 

Cheers!

 

 

  • erik_tarnvik's avatar
    erik_tarnvik
    Solution Specialist
    9 years ago

    That works! Pretty good for a Monday...

    • coathangers's avatar
      coathangers
      Frequent Visitor
      4 years ago

      For data that spans multiple years this would be more robust:

       

      IsCurrentMonth = IF(AND(MONTH('Table Name'[Date])=Month(NOW()),YEAR('Table Name'[Date])=YEAR(NOW())),1,0)

       

      Using 1 and 0 will work better in report service than text or boolean based filtering I find.