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

[Anonymous]

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!

[v-juanli-msft]

Hi @Anonymous 

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

[Anonymous]

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])

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

[v-juanli-msft]

Hi @Anonymous 

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

[Ashish_Mathur]

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.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

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

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.

[v-juanli-msft]

Hi @Anonymous 

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.

>