Forum Discussion

jdubs's avatar
jdubs
Helper V
1 year ago
Solved

Quick Measure --> Filter by Relative Date?

When creating a quick measure I can filter a column by a corresponding date column but it only allows me to select individual dates. How would I expand this to Relative Date e.g. last 12 months?

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi jdubs ,

     

    Please try this measure:

    Measure = 
VAR __today = TODAY()
VAR __start_date = EDATE(__today, -12)
VAR __result = CALCULATE([Count of Title],'Table'[createdon]>=__start_date && 'Table'[createdon]<__today+1)
RETURN
__result

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum 

3 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    Hello jdubs , 

     

    To have more control over your filter I would suggest to go with DAX measure like below..

     

    Measure Last 12 Months =
    CALCULATE (COUNT('Table'[title]),
    DATESINPERIOD('Table'[creationdate],MAX('Table'[creationdate]),-12,MONTH))

     

    Or if you still want to try the relative filter then go to Visual/Report Level filter and follow the below steps to create the same..

     

     

    1.Go to your Visual and select the filter pane.

    2. Add your Date Field (creation date in your case) to the filter pane.

    3.In the filter options, change it to "Relative Date Filtering."

    4.You can now set it to filter for the last 12 months or other ranges like days, weeks etc

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

     

     

  • jdubs's avatar
    jdubs
    Helper V
    1 year ago

    Hi Dharmendar,

     

    I tried this measure as you suggested but I get an error becuase there are duplicate dates in the date column (I actually tried this before making this post). I need to use a measure and not the visual filter becuase I am using multiple measures in a single table. 

     

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi jdubs ,

       

      Please try this measure:

      Measure = 
VAR __today = TODAY()
VAR __start_date = EDATE(__today, -12)
VAR __result = CALCULATE([Count of Title],'Table'[createdon]>=__start_date && 'Table'[createdon]<__today+1)
RETURN
__result

       

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly --  How to provide sample data in the Power BI Forum