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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate Resolved and Outstanding Tickets in Power BI Desktop.

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:

TaskNumberCreatedResolvedClosed
INC001043202/05/201419/08/201425/08/2014
RITM001020209/05/2014-12/05/2014
INC001270606/06/201401/07/201407/07/2014
INC001329519/06/201423/06/201423/06/2014
INC001401201/07/201401/07/201401/07/2014
REQ001265107/07/201407/07/201413/07/2014
INC001458711/07/201411/07/201411/07/2014
REQ001281711/07/201411/07/201411/07/2014
RITM001134223/07/2014-05/08/2014
INC001778222/09/201427/10/201427/10/2014
INC001868813/10/201413/10/201419/10/2014
INC001926523/10/201404/11/201410/11/2014
INC002019113/11/201413/11/201413/11/2014
REQ001778427/11/201428/11/201428/11/2014
INC002068401/12/201401/12/201401/12/2014
INC002098808/12/201408/12/201408/12/2014
INC002102608/12/201408/12/201408/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

   

1 ACCEPTED 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.

1.PNG

Please download the .pbix file for further analysis.

Best Regards,
Angelia

View solution in original post

6 REPLIES 6
DAX0110
Resolver V
Resolver V

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)

 

 

 

Anonymous
Not applicable

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


Anonymous
Not applicable

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.

1.PNG

Please download the .pbix file for further analysis.

Best Regards,
Angelia

Anonymous
Not applicable

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.

Outstanding Calcluation.png

 

Can you please help me.

 

Please let me know if you need any other information.

 

Regards,

Pavan Vanguri.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.