cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Active Tickets at any Point of Time while using Date Hierarchy

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:

 

IDCreatedResolved
104/01/2018 00:0015/04/2018 00:00
205/01/2018 00:0024/02/2018 00:00
305/01/2018 00:0002/03/2018 00:00
414/01/2018 00:0003/02/2018 00:00
529/01/2018 00:0029/01/2018 00:00
604/02/2018 00:0012/03/2018 00:00
708/02/2018 00:0015/02/2018 00:00
818/02/2018 00:0019/02/2018 00:00
903/03/2018 00:00 
1006/03/2018 00:00 
1101/04/2018 00:00 

 

Result: 

 

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

 

MonthOpened During the MonthClosed During the MonthActive at End of Month
Jan514
Feb343
Mar223
Apr113
3 REPLIES 3
LivioLanzo
Solution Sage
Solution Sage

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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?

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?

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors