show what has been added to a field between two dates

[beldona]

I have a simple table as follows.  I want to show what has been added between two selected dates in a filter. Note that dates might not be sequence - that is 8/1, 8//2, 8/3, 8/4, 8/5 etc.

So, if we selected the first date as 8/1 and the next date as 8/4; it should show that between these two dates, R4 and R5 is what got added.

Can you please help me how to do this?  Thank you much

Date	Product
8/1/2020	R1
8/1/2020	R2
8/1/2020	R3
8/2/2020	R1
8/2/2020	R2
8/2/2020	R3
8/2/2020	R4
8/4/2020	R1
8/4/2020	R2
8/4/2020	R3
8/4/2020	R4
8/4/2020	R5

[mahoneypat]

Here is a DAX measure expression that shows one way to do it.

Is New =
VAR mindate =
MIN ( 'Product'[Date] )
VAR pastrows =
CALCULATE (
COUNTROWS ( 'Product' ),
ALL ( 'Product'[Date] ),
'Product'[Date] < mindate
)
RETURN
IF ( ISBLANK ( pastrows ), 1 )

Here is the result if you try it.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[beldona]

@mahoneypat 

This is amazing!!  What you are showing is what I actually want.  I am so new to DAX and don't understand the inner workings of it yet.

If my data is in Excel and Sheet1 with Product and Date as the two columns, how does the DAX code you have provided change.  

Please help

[mahoneypat]

Assuming none of your table or column names change, it will automatically update when you refresh your data.

Basically, how it works it that it first gets the minimum value from the slicer in the variable, and then looks for the presence of any rows for that product (the table visual is providing a filter on product) before that date.  If it doesn't find any, it is considered new.

Regards,

Pat

[beldona]

@mahoneypat 

Thank you.  So when I copy and paste your DAX code it gives me error messages.  

Again, I get my data from Excel, Sheet1 which has a column called Product and a column that has Date.  Would your DAX code that you have provided when I copy and paste would I have to change anything?  

Please help @mahoneypat 

[mahoneypat · Accepted solution]

What error(s) are you seeing?  Please send a screen grab.  Is your query/table called Product?  Or still Sheet1?  The name of your table should replace "Product" throughout that expression.

Regards,

Pat

[beldona]

@mahoneypat 

Thank you thank you.  That works and you saved me so much time.  Kudos!!!!!

[mahoneypat]

If you make a Table visual with your Product column, and a Date slicer with your date column, you should get your desired result.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[beldona]

@ mahoneypat

The date slicer only shows that date.  What I am seeking is if I selected two different dates (two slicers) to only show what is different between the two dates for the field Product.

[mahoneypat]

If you change your date slicer to the Between view, you can pick a range of dates.  In the slicer, pull down the arrow in upper right to change it.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[beldona]

@mahoneypat 

What I need is what is different.  When I select between the two dates it shows me all the values in the Product as below

[v-xicai]

Hi @beldona ,

Generally to say, when you filter some values of a field, the relative values will be returned for other related fields.

Assuming the column [Date] is in Table1 , and the column [Product] is in Table2, then the reason why "R4" and "R5" haven't be display may be that the two tables have single of Cross filter direction in relationship.  Then you may change the Single to Both for Cross filter direction in relationship, which will take these tables treated as a single table.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[beldona]

@v-xicai It is not working.  Date is in the same table as the Product.

Any help would be appreciated.  I want to use what @mahoneypat  suggested, dates between and based on that some visual that will only list the unique value.  For example, if the date selected is 8/3 and 8/4, the visual (might be a table) shows R5.

Thanks