Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
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
Hi @AsherB,
If you can please share some sample data to test, it will be help for analysis.
Regards,
Xiaoxin Sheng
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').
Asher
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |