cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mchow1 Regular Visitor
Regular Visitor

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           Status

Product A        1/1/19      In production

Product A        2/1/19      Shipped

Product A        3/1/19      Delivered

Product B        7/1/19      In production

Product B        8/1/19      Shipped

Product B        9/1/19      Delivered

 

and I would like to essentially filter out all other rows that are furthest away from today, so that the output that looks similar to:

 

Product            Date           Status

Product A       3/1/19       Delivered

Product B       8/1/19       Shipped

1 ACCEPTED SOLUTION

Accepted Solutions
tejaswidmello Established Member
Established Member

Re: Filter data by dates closest to today

Hi @mchow1 ,

 

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:

 

Capture4.PNG

 

Let me know if this works for you.

 

Thanks,

Tejaswi

View solution in original post

9 REPLIES 9
tejaswidmello Established Member
Established Member

Re: Filter data by dates closest to today

Hi @mchow1 ,

 

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 :

Capture2.PNG

 

Let me know if this works for you.

 

Thanks,

Tejaswi

mchow1 Regular Visitor
Regular Visitor

Re: Filter data by dates closest to today

Hi @tejaswidmello,

 

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?

tejaswidmello Established Member
Established Member

Re: Filter data by dates closest to today

Hi @mchow1 ,

 

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:

 

Capture4.PNG

 

Let me know if this works for you.

 

Thanks,

Tejaswi

View solution in original post

mchow1 Regular Visitor
Regular Visitor

Re: Filter data by dates closest to today

Hi @tejaswidmello,

 

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

tejaswidmello Established Member
Established Member

Re: Filter data by dates closest to today

Hi @mchow1 ,

 

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

mchow1 Regular Visitor
Regular Visitor

Re: Filter data by dates closest to today

@tejaswidmello,  

Sample input data would essentially be the same as above. Since your previous formula displayed which status each product belonged to based on date, I was wondering if there was a way to count how many times each status occurred based on this information, regardless of product.

tejaswidmello Established Member
Established Member

Re: Filter data by dates closest to today

Hi @mchow1 ,

 

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

mchow1 Regular Visitor
Regular Visitor

Re: Filter data by dates closest to today

Hi @tejaswidmello,

 

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!

tejaswidmello Established Member
Established Member

Re: Filter data by dates closest to today

Hi @mchow1 ,

 

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)

Capture2.PNG

And below output with the closest date :

Capture1.PNG

 

Thanks,

Tejaswi

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)