Another way to calculate backlog

[tommasocitton]

Hi all,
I'vre tried to search but all examples seems not to fit my needs...

I've got two table:
CALENDAR (classical virtual table with all dates in the last 2 years)
INCIDENT_LOG ("n" rows per incident, ordered by date, with the status of the incident); these are the basics columns:
INC_NUMBER
DATE (date+time)
STATUS (usually Assigned, In Progress, Resolve, Pending, Closed)

Considering the following data in table INCIDENT_LOG (for each ticket there are only dates with a status change):
INC1;2017-10-01 11.00;Assigned
INC1;2017-10-01 13.00;In progress
INC1;2017-10-04 14.00;Closed
INC2;2017-10-02 13.00;Assigned
INC2;2017-10-02 13.00;Closed
INC3;2017-10-02 11.00;Assigned
INC3;2017-10-02 14.00;In progress
INC3;2017-10-10 11.00;Closed

I'd like to have a calculate tabel CALENDAR_BACKLOG with the snapshot at the end of day, as follows (columns DATE;INC_IN_BACKLOG):
2017-09-30;0 (no ticket)
2017-10-01;1 (INC1)
2017-10-02;2 (INC1+INC3; INC2 is not in backlog at the end of day)
2017-10-03;2 (INC1+INC3)
2017-10-04;1 (INC3; INC1 is not in backlog at the end of day)
2017-10-05;1 (INC3)
2017-10-06;1 (INC3)
2017-10-07;1 (INC3)
2017-10-08;1 (INC3)
2017-10-09;1 (INC3)
2017-10-10;0 (INC3 is not in backlog at the end of day)

Any ideas?

Thanks in advcance everybody!
Tommaso

[tommasocitton]

I'm trying to reply and add the solution I've found, but all my messages go in:

My Unmoderated Items

• My Unmoderated Items : My Rejected Items

Why?

[tommasocitton]

Hi all,

maybe I've found a solution, also thanks to your suggestions that guided me and helped me to change my point of view on data.

1. I've created a new table TT_LOG in input with INCIDENT, DATE, IN_OUT (a flag IN or OUT) --> for each incident I know when it has entered or exited my "area" (my backlog)
2. New two calculated tables from previous TT_LOG:
   ENTRATI = CALCULATETABLE(TT_LOG;FILTER(TT_LOG;TT_LOG[IN_OUT]="IN")) --> each date a ticket has entered
   USCITI = CALCULATETABLE(TT_LOG;FILTER(TT_LOG;TT_LOG[IN_OUT]="OUT")) --> each date a ticket has exited 
3. New calculated table composed by  table ENTRATI and for each row the next date of USCITI (since IN and OUT are sequential in the timeline):
   TICKET_INOUT = SUMMARIZE(ENTRATI;ENTRATI[INCIDENT_NUMBER];ENTRATI[AREA];ENTRATI[LOG_DATE])
   with two added columns:
   DATE_OUT =
      CALCULATE(
         MIN(USCITI[LOG_DATE]);
         FILTER(
            USCITI;
            USCITI[INCIDENT_NUMBER]=TICKET_INOUT[INCIDENT_NUMBER]&&USCITI[LOG_DATE]>TICKET_INOUT[DATE_IN]
         )
      )
   DATE_OUT2 = if(TICKET_INOUT[DATE_OUT]=BLANK();DATEVALUE("31/12/2999");TICKET_INOUT[DATE_OUT])
4. The new final table:
   BACKLOG2 = SUMMARIZE(zdate;ZDate[Date])
   with an added column like suggested:
   INC_IN_BACKLOG =
      COUNTROWS (
         FILTER (
            SUMMARIZE (
            TICKET_INOUT;TICKET_INOUT[INCIDENT_NUMBER];TICKET_INOUT[AREA];TICKET_INOUT[DATE_IN];TICKET_INOUT[DATE_OUT]
   );
            TICKET_INOUT[DATE_IN] <= BACKLOG2[Date]
               && (TICKET_INOUT[DATE_OUT] > BACKLOG2[Date] || TICKET_INOUT[DATE_OUT] = BLANK())
         )
      ) + 0

[v-ljerr-msft]

Hi @tommasocitton,

Great to hear you have found the solution! Could you accept your reply as solution to close this thread? 

Regards

[tommasocitton · Accepted solution]

Of course!

Thanks for the support you gave me! 🙂

Tommaso

[tommasocitton]

ciao @v-ljerr-msft

maybe I've found a next-step to think about...

What if a ticket exits and the the re-enters my backlog once more?

I mean...

Suppose the status CLOSED is not the final one and after 3-4 days, the ticket once closed is then re-opened...

The "table" SUMMARIZE only considers min and max dates in the whole life cycle of the ticket, doens't it?

🙂 

Tom

[v-ljerr-msft]

Hi @tommasocitton,

Based on my test, you should be able to use the formula below to add a new calculate column in your CALENDAR table to calculate INC_IN_BACKLOG in your scenario. 

INC_IN_BACKLOG = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
            INCIDENT_LOG,
            INCIDENT_LOG[INC_NUMBER],
            "First_Date", CALCULATE ( MIN ( INCIDENT_LOG[DATE] ) ),
            "Last_Date",
            VAR maxDate =
                CALCULATE ( MAX ( INCIDENT_LOG[DATE] ) )
            VAR lastStatus =
                CALCULATE (
                    LASTNONBLANK( INCIDENT_LOG[STATUS], 1 ),
                    INCIDENT_LOG[DATE] = maxDate
                )
            RETURN
                IF ( lastStatus = "Closed", maxDate - 1, maxDate )
        ),
        DATEVALUE([First_Date]) <= 'CALENDAR'[Date]
            && DATEVALUE([Last_Date]) >= 'CALENDAR'[Date]
    )
) + 0

Here is the sample pbix file for your reference. 

Regards

[tommasocitton]

It seems to work.

I'm working on it to adapt to other filters I need, but you could have suggested me the right way to sort the problem out.

I'll keep back with my news asap.

Thanks again!!

Tommaso

[emadrigal]

what is your datasource with tommasocitton solution should work., have you tried creating a calculated table too, filtering everying but closed cases?

[tommasocitton]

Hi emadrigal.

I'm not so sure to understand what you mean.

Considering I'm quite a newbie in DAX, off course I suppose I can create a calculated table and filter it, but I'm not able to realize it in mind first.

I mean... the output should just be another table with the count per day of all tickets for which the last status (for date <= on observation date) is <> Closed. I've tried to make an example in sql-style.

[emadrigal]

you need to create a calculated column using the calculate formula using a filter there., here is an example:

you could have this column on the calendar table or a new one because you want to count items per day.

Column = CALCULATE(COUNT(item[itemId]]),status <> 'closed')

[tommasocitton]

Just to give an example, if it was a database I would have create a query like that:

select

DATE, (

     select count(distinct INCDENT_NUMBER) from INCIDENT_LOG B

     where B.STATUS <> 'Closed'

     and B.DATE = (

          select max(DATE) from INCIDENT_LOG C

          where C.INCIDEN_NUMBER = B.INCIDENT_NUMBER

          and C.DATE <= A.DATE

     )

) as count_of_inc_in_backlog_at_date

from CALENDAR A

CALENDAR is the main table and for each date it counts the number of ticket for which the last status at that date is not closed.

I hope it helps.

Tommaso