Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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
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.
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
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.
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.
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.
>
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |