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 measure.

and put status and this measure in a table Visualization to get the output you are looking for.

 

Measure 7 = calculate(counta('Table (3)'[Status]))

 

Let me know if this works.

Thanks,

Tejaswi

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous,

 

With my data set, this returns all products with that status (so, if there are 15 products, it will return 15 in In Production, 15 in Shipped, and 15 in Delivered). I realize that my initial question might have been a little confusing, so I hope I can clarify that here. 

 

As an example, I'm going to add on to the previous output we generated. I suppose either the original output or this output could work as the sample input, whichever would be easier to work with.

Product        Status            Date

A                Delivered        3/1/19

B                 Shipped         8/1/19

C                Delivered        4/1/19

D             In Production    7/1/19

E                 Shipped         6/1/19

Is there a way to create a measure/measures to count the number of products with a certain status or count the number of times each status appears? Either way, I'm hoping to get a sample output of Delivered=2, Shipped=2, and In Production=2. 

 

Thank you for your time!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

     

    I used your very first post example and I get what you are looking for.

     

    See below my output:( based on the output you were looking in your previous post)

    And below output with the closest date :

     

    Thanks,

    Tejaswi