Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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
v-juanli-msft
Community Support
Community Support

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

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.

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

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
Not applicable

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. 

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-juanli-msft
Community Support
Community Support

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

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.

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.

>

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.