Forum Discussion

peffany's avatar
peffany
Frequent Visitor
3 years ago
Solved

How to filter by oldest date

Hello there,

 

I'd like to filter a card visual by the oldest date. It seems like the only options i'm given are filter by date intervals.

 

Below I put a screenshot of the Filter pannel with two dates as an example. I always want / only want the oldest date selected at all times as the data changes everyday. 

 

Thank you in advance for your help. Regards.

 

Peffany 

 

Need Help
  • halfglassdarkly's avatar
    halfglassdarkly
    3 years ago

    Ok, I should have tested that sorry, this is working for me:

    Date filter =
If(Calculate(FIRSTDATE('Total'[Date Livraison]),ALL('Total')) = FIRSTDATE('Total'[Date Livraison]),"TRUE","FALSE")

     

    Note I had to encase "TRUE" and "FALSE" in quotes, turning them into text for the measure to work.

  • peffany's avatar
    peffany
    3 years ago

    Thanks for the rectification. I haven't tried your method as I've found an alternative one. I created a function column (not sure how you call it...) : 

     

    Color Alert =
    VAR today = TODAY()
    VAR alert_date = [Date Livraison]
    RETURN
     SWITCH(
        TRUE(),
        today > alert_date, "Red",
        BLANK()
        )
     
     
     
    Thanks for your help. I do appreciate it.
     

4 Replies

  • halfglassdarkly's avatar
    halfglassdarkly
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    Try creating a measure as a date filter using something like:

     

    Date filter =
'Dates'[Date Livraison] = FIRSTDATE('Dates'[Date Livraison])

     

     Add it as a filter to your visual/page and filter to TRUE

    • peffany's avatar
      peffany
      Frequent Visitor
      3 years ago

      Thanks for your quick reply I appreciate it. 

       

      I'm getting a syntax error for the measure you've given me. I can't seem to troubleshoot it. If you have any further suggestions I would be greatly appreciative. For info my table's name is "Total"

       

      Peffany.

       

       

  • halfglassdarkly's avatar
    halfglassdarkly
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    Ok, I should have tested that sorry, this is working for me:

    Date filter =
If(Calculate(FIRSTDATE('Total'[Date Livraison]),ALL('Total')) = FIRSTDATE('Total'[Date Livraison]),"TRUE","FALSE")

     

    Note I had to encase "TRUE" and "FALSE" in quotes, turning them into text for the measure to work.

    • peffany's avatar
      peffany
      Frequent Visitor
      3 years ago

      Thanks for the rectification. I haven't tried your method as I've found an alternative one. I created a function column (not sure how you call it...) : 

       

      Color Alert =
      VAR today = TODAY()
      VAR alert_date = [Date Livraison]
      RETURN
       SWITCH(
          TRUE(),
          today > alert_date, "Red",
          BLANK()
          )
       
       
       
      Thanks for your help. I do appreciate it.