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 need a way to capture all the open cases before the first date in a slicer to capture a case backog. For instance, if the slicer is set between 10/1/2020 and 12/31/2020, I would like to see all the cases that are still open that were created before 10/1/2020.
This is as close as I've gotten but I don't think I'm headed in the right direction. This should count all open cases and the subtract all the ones within the current date range. The problem with this is that it also captures those cases created after the end date of the slicer (12/31/2020 in the example above).
Date Driven Backlog = calculate(count('Case'[CaseNumber]),removefilters(RT_Dates[Date]),'Case'[IsClosed]=False)-CALCULATE(count('Case'[CaseNumber]),'Case'[IsClosed]=FALSE())
I can't share the actual file but I tried to recreate the issue in this Sample file. On the "Problem" tab I have a prodcut slicer and a date sclicer and a single card. The result I'm trying to achieve in the card is 5. This should be all the orders submitted before 4/15/1998 that have not yet shipped. The expected results are on the "Expected Tab".
Thanks in advance for your help.
Solved! Go to Solution.
In your example you say you expect a result of 5
However if you count the order ids of the products before 15-4 you only get 2 order ids
Here is what i did to help you along:
I took the Min of your date filter. MIN (Date[Date])
I want to get all open orders before that date
My solution works. If not say what is missing
It works perfectly. I accepted it as the solution. If you need me to do something else so you recevie credit for solving it I'm happy to do so. Please let me know if there is something else you need me to do.
In your example you say you expect a result of 5
However if you count the order ids of the products before 15-4 you only get 2 order ids
Here is what i did to help you along:
I took the Min of your date filter. MIN (Date[Date])
I want to get all open orders before that date
@Anonymous - Thank you. This appears to be exactly what I needed. Thank you for your help.
@Bentley , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@amitchandak - Thank you for the reply. Would the sample file in the original post work or would you need to see something else. While the data is not the same, it does replicate the problem I'm trying to solve for. Thank 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 |
---|---|
116 | |
102 | |
78 | |
77 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |