Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter data by dates closest to today

Hello,    I'd like to find the closest date to today in order to "tag" a product with its respective label. For example, I have a table of dates, set up like so:   Product            Date        ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous ,

     

    Try this DAX Column:

    Closest Date = VAR LatestDate= CALCULATE(
    MAX('Table (3)'[Date]),

    FILTER('Table (3)','Table (3)'[Date] < TODAY() &&
    'Table (3)'[Product] = earlier('Table (3)'[Product])),'Table (3)'[Date])
return
if('Table (3)'[Date]=LatestDate,1,0)

    Put the Dax column in the visual level filter and filter for 1.

     

    My output:

     

     

    Let me know if this works for you.

     

    Thanks,

    Tejaswi

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

 

try this Steps:

 

Add this formula in a DAX column:

Close date to today = 
VAR LatestDate = MAXX(FILTER('Table (3)','Table (3)'[Product] = EARLIER('Table (3)'[Product])),'Table (3)'[Date])
RETURN IF('Table (3)'[Date]=LatestDate,1,0)

Than add Date, Product and Status in the table visualuation.

Add the new calculated to the Visual level  filter only of this table and apply filter to show like this :

 

Let me know if this works for you.

 

Thanks,

Tejaswi

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous,

 

Thank you for your help! Your solution does work, but I'm looking to find the date value closest date to TODAY() and associate the corresponding "tag" to it. Your solution returns the last date catalogued per product, so it always returns the Delivered dates. Some of the products are Shipped or In Production, since those are the dates closest to TODAY(). Is there a way to show that or modify your formula to do this?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

     

    Try this DAX Column:

    Closest Date = VAR LatestDate= CALCULATE(
    MAX('Table (3)'[Date]),

    FILTER('Table (3)','Table (3)'[Date] < TODAY() &&
    'Table (3)'[Product] = earlier('Table (3)'[Product])),'Table (3)'[Date])
return
if('Table (3)'[Date]=LatestDate,1,0)

    Put the Dax column in the visual level filter and filter for 1.

     

    My output:

     

     

    Let me know if this works for you.

     

    Thanks,

    Tejaswi

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous,

       

      I really appreciate the help. Not sure if this is entirely related to my previous question, but is there was a way to count how many products are in each status as well? 

      Example output:

      In Production    4

      Shipped            10

      Delivered          5

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

         

        Can you give me a sample input data?

         

        and do you want the count to be based on each Product ( ie. Product A count , Product B count or regardless of product?

         

        Thank,s

        Tejaswi