need help with getting my data visualized properly

[CJLittle]

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.

[CJLittle]

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.

[v-xiaotang · Accepted 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.

[CJLittle]

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())))

[v-xiaotang]

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

[v-xiaotang]

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.