Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello all. I have some data that is in a format similar to the following:
Ticket Number | Opened Date | Closed Date |
1 | 4/6/2021 | 5/2/2021 |
2 | 4/6/2021 | 4/7/2021 |
3 | 4/8/2021 | 4/15/2021 |
4 | 4/12/2021 | 5/7/2021 |
5 | 4/13/2021 | 4/18/2021 |
6 | 4/19/2021 | 4/22/2021 |
7 | 4/25/2021 | 5/10/2021 |
8 | 4/25/2021 | 5/1/2021 |
9 | 4/29/2021 | 5/1/2021 |
10 | 4/30/2021 | 5/3/2021 |
What I need to do is know how many cases were in not closed on a particular date. I can do that by knowing counting the number of rows where the opened date is less than or equal to the particular date and the closed date is greater than the particular date.
For example, if I pick 4/10/2021 as the particular date (I also need to be able show this data for all dates, not just dates for which there are Opened and Closed Dates present), I should have a count of two rows (Ticket 1 and Ticket 3). Essentially, I need something that looks similar to the following where could then query on the Date and Open Tickets columns to know how many tickets were open on any particular date:
Ticket Number | Date | Opened Date | Closed Date | Open Tickets |
4/5/2021 | 0 | |||
1 | 4/6/2021 | 5/2/2021 | ||
2 | 4/6/2021 | 4/6/2021 | 4/7/2021 | 2 |
4/7/2021 | 1 | |||
3 | 4/8/2021 | 4/8/2021 | 4/15/2021 | 2 |
4/9/2021 | 2 | |||
4/10/2021 | 2 | |||
4/11/2021 | 2 | |||
4 | 4/12/2021 | 4/12/2021 | 5/7/2021 | 3 |
5 | 4/13/2021 | 4/13/2021 | 4/18/2021 | 4 |
4/14/2021 | 4 | |||
4/15/2021 | 3 | |||
4/16/2021 | 3 | |||
4/17/2021 | 3 | |||
4/18/2021 | 2 | |||
6 | 4/19/2021 | 4/19/2021 | 4/22/2021 | 3 |
4/20/2021 | 3 | |||
4/21/2021 | 3 | |||
4/22/2021 | 2 | |||
4/23/2021 | 2 | |||
4/24/2021 | 2 | |||
7 | 4/25/2021 | 5/10/2021 | 3 | |
8 | 4/25/2021 | 4/25/2021 | 5/1/2021 | 4 |
4/26/2021 | 4 | |||
4/27/2021 | 4 | |||
4/28/2021 | 4 | |||
9 | 4/29/2021 | 4/29/2021 | 5/1/2021 | 5 |
10 | 4/30/2021 | 4/30/2021 | 5/3/2021 | 6 |
5/1/2021 | 4 | |||
5/2/2021 | 3 | |||
5/3/2021 | 2 |
I can't seem to wrap my head around how to do this in Power BI though. I've tried a few different measures where I'm calculating the count with a filter but it never gives me anything close to what I would expect. I've gotten closer by creating a date table using the calendar function and then a table visual which looks similar to the following:
Ticket Number | Date | Opened Date | Closed Date |
4/5/2021 | |||
1 | 4/6/2021 | 4/6/2021 | 5/2/2021 |
2 | 4/6/2021 | 4/6/2021 | 4/7/2021 |
4/7/2021 | |||
3 | 4/8/2021 | 4/8/2021 | 4/15/2021 |
4/9/2021 | |||
4/10/2021 | |||
4/11/2021 | |||
4 | 4/12/2021 | 4/12/2021 | 5/7/2021 |
5 | 4/13/2021 | 4/13/2021 | 4/18/2021 |
4/14/2021 | |||
4/15/2021 | |||
4/16/2021 | |||
4/17/2021 | |||
4/18/2021 | |||
6 | 4/19/2021 | 4/19/2021 | 4/22/2021 |
4/20/2021 | |||
4/21/2021 | |||
4/22/2021 | |||
4/23/2021 | |||
4/24/2021 | |||
7 | 4/25/2021 | 4/25/2021 | 5/10/2021 |
8 | 4/25/2021 | 4/25/2021 | 5/1/2021 |
4/26/2021 | |||
4/27/2021 | |||
4/28/2021 | |||
9 | 4/29/2021 | 4/29/2021 | 5/1/2021 |
10 | 4/30/2021 | 4/30/2021 | 5/3/2021 |
5/1/2021 | |||
5/2/2021 | |||
5/3/2021 | |||
5/4/2021 | |||
5/5/2021 |
and while I now have a complete list of dates for the month, I still can't seem filter between the "date" value and the "closed date" value.
Any thoughts on how to pull this off would be greatly appreciated. Thanks.
Solved! Go to Solution.
thanks for your reply @CJLittle
sure, try to change 'Table'[Closed Date] >= _seldate to Table'[Closed Date] > _seldate in measures
count =
var _seldate=SELECTEDVALUE(calendarTable[Date])
return countrows(filter('Table', 'Table'[Opened Date]<=_seldate &&'Table'[Closed Date]>_seldate))
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
This seems like it might be closer to what I want but isn't taking into account the fact that ticket number 3 was closed on 4/15, reducing the total number of tickets open on 4/15 to 3 from 4.
thanks for your reply @CJLittle
sure, try to change 'Table'[Closed Date] >= _seldate to Table'[Closed Date] > _seldate in measures
count =
var _seldate=SELECTEDVALUE(calendarTable[Date])
return countrows(filter('Table', 'Table'[Opened Date]<=_seldate &&'Table'[Closed Date]>_seldate))
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much. This got me almost all the way there. I just had to add in an or statement around the closed date to check for null values too. The final measure looks like the following:
count =
var _seldate=SELECTEDVALUE(calendarTable[Date])
return countrows(filter('Table', 'Table'[Opened Date]<=_seldate && ('Table'[Closed Date]>_seldate || 'Table'[Closed Date] = BLANK())))
Hi @CJLittle
Have you solved this question? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
Any question, please let me know. Looking forward to receiving your reply.
Best Regards,
Community Support Team _Tang
Hi @CJLittle
Your solution looks a bit complicated, would you consider trying an alternative? 😜 it will be simpler.
-
try this measure
count =
var _seldate=SELECTEDVALUE(calendarTable[Date])
return countrows(filter('Table', 'Table'[Opened Date]<=_seldate &&'Table'[Closed Date]>=_seldate))
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |