Active Tickets at any Point of Time while using Date Hierarchy

[Anonymous]

HI Friends,

I am having dataset like below. Ticket Number, Created Date, Resolved Date. I have designed a report in Tableau to show Number of Tickets Opened During the Month, Resolved During the Month and Active @ End of Month (Ticket not closed during the month and carried over to next Month). I tried all my possibilites but i have no clue on how to achive this in PBI. 

I have read articles which narrows down a query pointing to a Generic Month field. But I need to be able to use the Drill Down / Date Hierarchy while showing this. Example: Year - Opened Bar, Closed bar, Active @ EOM bar. Then if I drill down to go inside Qtr then I should drill down to months and so on. I am completely clueless. In Tableau this is called Running Sum but in PBI I dont know if this is Running Total or Cumulative Sum or etc.,  But I dont have any ideas further.

Date:

ID	Created	Resolved
1	04/01/2018 00:00	15/04/2018 00:00
2	05/01/2018 00:00	24/02/2018 00:00
3	05/01/2018 00:00	02/03/2018 00:00
4	14/01/2018 00:00	03/02/2018 00:00
5	29/01/2018 00:00	29/01/2018 00:00
6	04/02/2018 00:00	12/03/2018 00:00
7	08/02/2018 00:00	15/02/2018 00:00
8	18/02/2018 00:00	19/02/2018 00:00
9	03/03/2018 00:00
10	06/03/2018 00:00
11	01/04/2018 00:00

Result: 

I am showing it in a Tabular format but actually want a bar chart with these three measures.

Month	Opened During the Month	Closed During the Month	Active at End of Month
Jan	5	1	4
Feb	3	4	3
Mar	2	2	3
Apr	1	1	3

[LivioLanzo]

Hi @Anonymous 

I have uploaded a pbix file for you to take a look at:

https://1drv.ms/u/s!AiiWkkwHZChHj1sEKdKMxX4EHVli

do let me know if any questions

[vtechz]

Hi Thanks a lot for your valuable solution. I am able to reproduce the same in my PROD data. But the drill down is not happening. If you remove the month in your PBIX the counts are disappearing. If I am adding the MONTH again then the counts are coming up. any idea please?

[LivioLanzo]

Hi @vtechz 

thanks for the feedback. Yes the measure is meant to work as a monthly count, to what you'd like to drill?  days?