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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors