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.
I am trying to write a filter for a [Due Date] column within my dataset, I have a [DateKey] for a date heirarchy listing. My goal is to determine a few things,
If Due Date is less than Today() = 'Past Due'
If Due Date equals Today() = 'Due Now'
If Due Date is greater than Today() but not greater than itself = 'Upcoming'
Example
Due Date Status 10/1/2017 Past Due 10/31/2017 Upcoming 10/25/2017 Due Now 10/20/2017 Past Due 12/31/2017
Ultimately I am attempting to filter/show only relevant date pre-due date within my visual and exclude anything beyond a due date. I know this can be achieved within a slicer however being that I have multiple orders I am looking at, a slicer isn't really an option.
Solved! Go to Solution.
I think I have finally figured out a solution, what I ended up doing was creating an IF measure which determined if my receive date was greater than my ship date it would return "1". After which I then created new IF measures for each of my values which referenced my shipping measure and if a 1 was present it would be blank, otherwise it would display it's data.
The matrix visual seems intelligent enough to then omit those blank rows thus not displaying unwanted data.
Thank you all for your help, I couldn't of got here without you.
@adamnimmo you can create a calculated column with a switch statement and then filter by your values
i.e
filter =
SWITCH (
duedate < TODAY (), "Past Due",
duedate = TODAY (),"Due Now",
duedate > today, "Upcoming"
)
Proud to be a Super User!
Thanks @vanessafvg
Anytips on how I could also filter out that which is beyond the due date?
Essentially this
If Due Date is greater than Due Date = 'Not Due'
I have been racking my brain looking for a solution, but with no anchor point to compare I am a bit lost.
@adamnimmo i am a bit confused how can it be > than itsself, or is it 2 different fields? what defines the due date and how can it be greater than itself.?
Proud to be a Super User!
@vanessafvg My due date is pre-determined by order numbers, my ultimate goal here is to report all current open orders and then using a matrix visual display items specific to each order, the complication is with the items being rather common and shared across multiple orders and the due date is the only point which really determines if those items should be listed or not.
I am certainly open to trying anything or researching a different way to approach the problem. I am also share test data, however I am not able to share the real pbix/dataset as the information is company owned.
@adamnimmo the question i am asking though is how will you identify if a due date > than itself, what do you have to compare, what are you comparing it against? you need some sort of indicator, without seeing data or dummy data,its hard to understand how this condition can be crafted
Proud to be a Super User!
Here is an example set of data, I shrunk the views down for this. Basicially when you drill into an order number it will show an equipment model, then it will show the incoming items for the order. I am wanting to exclude anything that is 'Receiving' after the 'Ship' date.
example.pbix
I think I have finally figured out a solution, what I ended up doing was creating an IF measure which determined if my receive date was greater than my ship date it would return "1". After which I then created new IF measures for each of my values which referenced my shipping measure and if a 1 was present it would be blank, otherwise it would display it's data.
The matrix visual seems intelligent enough to then omit those blank rows thus not displaying unwanted data.
Thank you all for your help, I couldn't of got here without you.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |