Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors