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 new to power bi, I would like to calculate Created, Resolved and Outstanding Tickets in power bi.
Can any one please suggest how to achieve this in power bi.
Sample Data:
TaskNumber | Created | Resolved | Closed |
INC0010432 | 02/05/2014 | 19/08/2014 | 25/08/2014 |
RITM0010202 | 09/05/2014 | - | 12/05/2014 |
INC0012706 | 06/06/2014 | 01/07/2014 | 07/07/2014 |
INC0013295 | 19/06/2014 | 23/06/2014 | 23/06/2014 |
INC0014012 | 01/07/2014 | 01/07/2014 | 01/07/2014 |
REQ0012651 | 07/07/2014 | 07/07/2014 | 13/07/2014 |
INC0014587 | 11/07/2014 | 11/07/2014 | 11/07/2014 |
REQ0012817 | 11/07/2014 | 11/07/2014 | 11/07/2014 |
RITM0011342 | 23/07/2014 | - | 05/08/2014 |
INC0017782 | 22/09/2014 | 27/10/2014 | 27/10/2014 |
INC0018688 | 13/10/2014 | 13/10/2014 | 19/10/2014 |
INC0019265 | 23/10/2014 | 04/11/2014 | 10/11/2014 |
INC0020191 | 13/11/2014 | 13/11/2014 | 13/11/2014 |
REQ0017784 | 27/11/2014 | 28/11/2014 | 28/11/2014 |
INC0020684 | 01/12/2014 | 01/12/2014 | 01/12/2014 |
INC0020988 | 08/12/2014 | 08/12/2014 | 08/12/2014 |
INC0021026 | 08/12/2014 | 08/12/2014 | 08/12/2014 |
Desired Output:
When I select Common Date as follows.
Common Date Created Tickets Resolved Outstanding
May 2014 2 1 1
June 2014 2 1 2
July 2014 5 5 2
Sep 2014 1 0 1
Oct 2014 2 2 1
Nov 2014 2 3 0
Dec 2014 3 3 0
Solved! Go to Solution.
Hi @Anonymous,
There some errors in your desired result based on your logic. In July, the Outstanding should be 1, because there is RITM0011342 whose Resolved date is blank and Closed date>Created Date. In November, the Outing should be 1, REQ0017784 whose Reslved date>Created Date. So please follow the steps below, you will get right reply.
1. Create a calculated column to get Month of [Created] using the formula.
Month = MONTH(Table1[Created])
2. Create a new table only including [Month] column by clicking "New Table" under Modeling on Home page. Notice, there is no relationship between the two tables.
Month = SELECTCOLUMNS(Table1,"Month",Table1[Month])
3. Create two measures to get Created and Resolved Count.
COUNT = CALCULATE ( COUNT ( Table1[TaskNumber] ), FILTER ( Table1, Table1[Month] = SELECTEDVALUE ( 'Month'[Month] ) ) ) Resolved_count = COUNTX ( FILTER ( Table1, Table1[Resolved] = BLANK () && MONTH ( Table1[Closed] ) = SELECTEDVALUE ( 'Month'[Month] ) ), Table1[TaskNumber] ) + COUNTX ( FILTER ( Table1, MONTH ( Table1[Resolved] ) = SELECTEDVALUE ( 'Month'[Month] ) ), Table1[TaskNumber] ) + 0
4. Create a calculated column to recognize the outstanding Task as 1, otherwise 0.
Condition = IF ( Table1[Created] < Table1[Resolved], 1, IF ( ( ISBLANK ( Table1[Resolved] ) && Table1[Created] < Table1[Closed] ), 1, 0 ) )
5. Create a measure to get outstanding tickets.
Outsatanding_count = CALCULATE(SUM(Table1[Condition]),FILTER(Table1,Table1[Month]=SELECTEDVALUE('Month'[Month])))
6. Create a table, select 'Month'[Month] and three measures as values, you will get the desired result.
Please download the .pbix file for further analysis.
Best Regards,
Angelia
This can be achieved in the Query Editor of Power BI:
1. ,make sure there are no "non-date" values in any of the three date columns; you can use the "Replace Values" tool to remove the two "-" values in the "Resolved" column
2. select the TaskNumber column, and "Unpivot Other Columns"
3. rename "Attribute" to "Action" and "Value" to "Date", change data type of "Date" column to "date"
4. save
5. then you can use a Matrix with "Date" on rows and "Action" on columns, and count (distinct) of TaskNumber in values; expand to month level to obtain your desired output (you could filter out the blanks as well)
Hi DAX0110,
I'm looking to calculate the values of Resolved and Outstanding tickets.
If I do the same I will get the wrong results, can you please hellp me how to write DAX query to achieve this.
Hi @Anonymous,
Could you please share the calculation rules for Resolved and Outstanding tickets? Why value for Resolved in May 2014 is 1, why value for Outstanding in July 2014 is 2? Please share more details for better analysing.
Best Regards,
Angelia
Hi,
Sorry for the delay in response.
For the month of May 2014 there are 2 tickets created (INC0010432, RITM0010202), so Created = 2.
In the month of May 2014, out of 2 tickets, 1 ticket got closed (RITM0010202, 12/05/2014), so Resolved = 1, and the other is outstanding for the month of May 2014, so Outstanding = 1.
1. Logic for Created = No.of tickets created in the month.
Count({<CalendarMonthName={'$(=monthname(max(CalendarMonthName)))'}>}TaskNumber)
---------------------------------------------------------------------------------------------------------------------------------------------
2. Logic for Resolved = if ResolvedDate is null and ClosedDate = MonthSelected or ResolvedDate = MonthSelected then Count of Tickets.
Count({<Deriveddate={'$(=Date(Max(CalendarMonthName),'MMM YYYY'))'},CalendarMonthName>}
if(((IsNull(DateResolved) OR LEN(TRIM(DateResolved))<0) and DateClosed = Deriveddate)
or DateResolved = Deriveddate,TaskNumber))
----------------------------------------------------------------------------------------------------------------------------------------------
3. Logic for Outstanding = if CreatedDate <= MonthSelected and ResolvedDate > MonthSelected or ClosedDate > MonthSelected and isnull of ResolvedDate or isnull of ResolvedDate and isnull of ClosedDate then count of Tickets.
Count({<Deriveddate={">=$(=date(addmonths(max(CalendarMonthName),-11),'DD/MM/YYYY'))<=$(=date(addmonths(max(CalendarMonthName),0),'DD/MM/YYYY'))"},CalendarMonthName>}
if((DateCreated <= Deriveddate) and
((DateResolved > Deriveddate)
or ((DateClosed > Deriveddate) and (IsNull(DateResolved) OR LEN(TRIM(DateResolved))<0))
or ((IsNull(DateResolved) OR LEN(TRIM(DateResolved))<0) and (IsNull(DateClosed) OR LEN(TRIM(DateClosed))<0))
)
,TaskNumber))
---------------------------------------------------------------------------------------------------------------------------------------------
I'm replicating Qlik sense application into Power BI.
Please let me know if you need any additional information.
Hi @Anonymous,
There some errors in your desired result based on your logic. In July, the Outstanding should be 1, because there is RITM0011342 whose Resolved date is blank and Closed date>Created Date. In November, the Outing should be 1, REQ0017784 whose Reslved date>Created Date. So please follow the steps below, you will get right reply.
1. Create a calculated column to get Month of [Created] using the formula.
Month = MONTH(Table1[Created])
2. Create a new table only including [Month] column by clicking "New Table" under Modeling on Home page. Notice, there is no relationship between the two tables.
Month = SELECTCOLUMNS(Table1,"Month",Table1[Month])
3. Create two measures to get Created and Resolved Count.
COUNT = CALCULATE ( COUNT ( Table1[TaskNumber] ), FILTER ( Table1, Table1[Month] = SELECTEDVALUE ( 'Month'[Month] ) ) ) Resolved_count = COUNTX ( FILTER ( Table1, Table1[Resolved] = BLANK () && MONTH ( Table1[Closed] ) = SELECTEDVALUE ( 'Month'[Month] ) ), Table1[TaskNumber] ) + COUNTX ( FILTER ( Table1, MONTH ( Table1[Resolved] ) = SELECTEDVALUE ( 'Month'[Month] ) ), Table1[TaskNumber] ) + 0
4. Create a calculated column to recognize the outstanding Task as 1, otherwise 0.
Condition = IF ( Table1[Created] < Table1[Resolved], 1, IF ( ( ISBLANK ( Table1[Resolved] ) && Table1[Created] < Table1[Closed] ), 1, 0 ) )
5. Create a measure to get outstanding tickets.
Outsatanding_count = CALCULATE(SUM(Table1[Condition]),FILTER(Table1,Table1[Month]=SELECTEDVALUE('Month'[Month])))
6. Create a table, select 'Month'[Month] and three measures as values, you will get the desired result.
Please download the .pbix file for further analysis.
Best Regards,
Angelia
Hi,
I have made some changes to the calculations and is working fine.
Here is the calculation I'm using to the desired results.
Outstanding =
VAR DD = SELECTEDVALUE(dim_Task[CreatedDate])
RETURN
CALCULATE(COUNT(dim_Task[TaskNumber]),FILTER(ALL(dim_Task),(ISBLANK(dim_Task[TestDate]) && dim_Task[CreatedDate]<=DD)|| (dim_Task[CreatedDate]<=DD && dim_Task[TestDate]>DD)))
But the problem is, the calculation of Outstanding is working fine for Month dimension, but not working on any other dimensions.
Below is the screenshot for your reference.
Can you please help me.
Please let me know if you need any other information.
Regards,
Pavan Vanguri.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |