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

Compare Max value selected in date slicer to row context in table

Hello there.

 

I'd like to compare the max value of what is in my date slicer for Status Date. I'm using a BEFORE only slicer drop down so, the user just enters a single date in the filter. This is essentially an opening balance. It will show all products that were created on or before the created date/time filter, but I need to exclude items which have been shipped before the created date/time filter, as they are no longer considered on hand.

 

Every row in my table is at the product level, which contains a shipped date column. I'd like to see if the shipped date is before the status date. If it was shipped before the status date of the report, I want a column to flag this and it would ultimtealy be filtered out. Each item can have multiple statuses, and a date for each status. Basically we need to exclude Prod 2 and 3 from the grid. While they were creaed before 4/1, they were shipped out before 4/1 as well so they are not hand.

 

Created Status Date  = On or before 4/1/2019

 

Item      Shipped Date      Shipped Before        Created Status Date

Prod 1     4/2/2019                    N                               3/15/2019

Prod 2     3/26/2019                  Y                                3/21/2019

Prod 3     3/18/2019                  Y                                3/1/2019

 

Any pointers would be most excellent!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Compare Max value selected in date slicer to row context in table

Hi @Sicilian 

Create a date table

date table = CALENDARAUTO()

don't create a relationship between two tables.

 

Create measures in main table

Max date = MAX('date table'[Date])

create before = IF(MAX(Sheet1[Created Status Date])<[Max date],"Y","N")

shipped before = IF(MAX(Sheet1[Shipped Date])<[Max date],"Y","N")

Then add these two measures in the visual level filter of the table visual

1.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Community Support Team
Community Support Team

Re: Compare Max value selected in date slicer to row context in table

Hi @Sicilian 

Create a date table

date table = CALENDARAUTO()

don't create a relationship between two tables.

 

Create measures in main table

Max date = MAX('date table'[Date])

create before = IF(MAX(Sheet1[Created Status Date])<[Max date],"Y","N")

shipped before = IF(MAX(Sheet1[Shipped Date])<[Max date],"Y","N")

Then add these two measures in the visual level filter of the table visual

1.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User
Super User

Re: Compare Max value selected in date slicer to row context in table

Hi,

Create a relationship between the Status Date and Date column of the Calendar Table.  Drag the Date column from the Calendar Table to your slicer.  Select a date in the slicer.  Write this measure

=MAX(Data[Shipped Date])<MAX(Calendar[Date])

Hope this helps.

Community Support Team
Community Support Team

Re: Compare Max value selected in date slicer to row context in table

Hi @Sicilian 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

 

Best Regards

Maggie

Sicilian Regular Visitor
Regular Visitor

Re: Compare Max value selected in date slicer to row context in table

Hi,

 

I've tried both approaches, but they do not seem to work. If I do not add the relation for Date table, the slicer will not filter the data grid properly.

 

I tried the Shipped Before measure, but it will not add correctly to my table, it simply keeps spinning and times out.

 

Max Date = MAX('Date'[DateKey])
DateKey w Create Date.PNG
^Does not filter without the relationship to the "Status Date" table.
 
Edit: If I make the relationship, it filters the grid correctly and the Max Date works as expected. For some reason, I cannot add the Max Date measure to my table grid, it keeps spinning and times out. 
Community Support Team
Community Support Team

Re: Compare Max value selected in date slicer to row context in table

Hi @Sicilian 

In my solution, the slicer with date column wouldn't slicer any data of the table.

To make the data in the table visual filtered, i create two measures "create before" and "shipped before", 

add them in visual level filter as my screenshot.

You could dowmload my pbix file to test, it should work.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

>

Community Support Team
Community Support Team

Re: Compare Max value selected in date slicer to row context in table

Hi @Sicilian 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

 

Best Regards

Maggie