cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AsherB
New Member

Ticket Backlog

Hi,

 

I'm pretty new to Power Bi and learning a lot from answers given here.

I'm trying to create a ticket backlog.

 

Following are the relevant columns:

Table: 'case-export'

Case #

Created(creation date)

Resolved

'Case Custom Field 11 (Severity )'(I can also add it later as a filter, I believe it will be more simple)

 

Table: 'Calander'

Date

Month

 

The result I'm interested in is a backlog showing for each week, how many tickets were open("created" but not "resolved") and if possible by severity.

 

For example:

For week 34, we had 12 open tickets with "General" severity, 10 "minor", 5 "Major", 2 "Critical"

Just to show the trend we can use every Sunday as a week marker.

 

Thanks in advance,

Asher

6 REPLIES 6
AsherB
New Member

Ticket Backlog

Hi,

 

I'm pretty new to Power Bi and learning a lot from answers given here.

I'm trying to create a ticket backlog.

 

Following are the relevant columns:

Table: 'case-export'

Case #

Created(creation date)

Resolved

'Case Custom Field 11 (Severity )'(I can also add it later as a filter, I believe it will be more simple)

 

Table: 'Calander'

Date

Month

 

The result I'm interested in is a backlog showing for each week, how many tickets were open("created" but not "resolved") and if possible by severity.

 

For example:

For week 34, we had 12 open tickets with "General" severity, 10 "minor", 5 "Major", 2 "Critical"

Just to show the trend we can use every Sunday as a week marker.

 

Thanks in advance,

Asher

Community Support
Community Support

Re: Ticket Backlog

Hi @AsherB,

 

If you can please share some sample data to test, it will be help for analysis.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
AsherB
New Member

Re: Ticket Backlog

Hi @v-shex-msft,

 

Thank you for your comment, not sure what you were expecting.

I'll attach a screenshot of the tables.

 

If it helps, attached a single line from the raw data('case-export').

 

https://ufile.io/1fmgu

 

Asher

 

 


Ticket Backlog.PNG

 

Moderator v-qiuyu-msft
Moderator

Re: Ticket Backlog

Hi @AsherB,

 

Please Create a calculated column Week = WEEKNUM('Calendar'[Date],1) in Calendar table.

 

Then you can create a measure like below:

 

Measure = CALCULATE(COUNT('case-export'[Case#]),FILTER('case-export',MAX('Calendar'[Week])>=WEEKNUM('case-export'[Created]) && MAX('Calendar'[Week])<WEEKNUM('case-export'[Resolved])))

 

You an download the attached pbix file to have a look.

 

Best Regards,
QiuyunYu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AsherB
New Member

Re: Ticket Backlog

Hi @v-shex-msft

 

Thank you for the detailed solution but it doesn't return the correct number I'm afraid.

I've looked over the Power bi file you've attached and it seems to be working correctly.

 

On my file, it returns a much lower number than expected.

 

We have cases that remain open for few weeks, maybe that's the issue?

 

Can you explain to me the parts of the measure you've written?

 

Kind regards,

Asher

Moderator v-qiuyu-msft
Moderator

Re: Ticket Backlog

Hi @AsherB,

 

As you said it returns incorrect values in your pbix file? Would you please share your pbix file with us if possible? Or you can use some dummy data to clarify the issue. 

 

The DAX I written is to compare each calendar week for each record's start week and end week to decide whether it's open in current calendar week. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors