Flow-In / Flow-Out chart

[_n_MarianLein]

Hello all,

I am trying to create a chart that would show changes in service tickets.

I do have a dataset that shows me the ticket numbers for each day, i.e.

• Ticket 10001000 | 2019-10-01
• Ticket 10001001 | 2019-10-01
• Ticket 10001002 | 2019-10-01
• Ticket 10001000 | 2019-10-02
• Ticket 10001001 | 2019-10-02
• Ticket 10001003 | 2019-10-02

What I would like my chart to show not only the sum of distinct ticket numbers per day, but also, that on 2019-10-02, there was one ticket closed and one new ticket opened...

Would there be a way to show the inflow / outflow of tickets in a chart?

I think what I would need to do is to create some sort of comparison between (current_date) and ((current_date) - 1) where I would be comparing the distinct ticket numbers and highlight the differences. I know how I could do this in Excel, but I certainly wouldn't want to get back on that train... Any help would be appreciated!

[v-frfei-msft]

Hi @_n_MarianLein ,

To create two measures as below.

count = CALCULATE(DISTINCTCOUNT('Table'[Ticket I
D]),ALLEXCEPT('Table','Table'[Date]))

difference = 
VAR d =
    MAX ( 'Table'[Date] ) - 1
VAR c =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Ticket ID] ),
        ALLEXCEPT ( 'Table', 'Table'[Date] )
    )
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Ticket ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = d )
    )
RETURN
    IF ( a = BLANK (), BLANK (), c - a )

[_n_MarianLein]

Hi,

Unfortunately this does not do what I want it to do - The measure delivers some numbers, but they don't make sense when I look at the date.

What happes for me is that the "Difference" always shows a value above what I would be expecting...

Spoiler

"Difference" plotted as bar graph below the corresponding datesdates

To better visualize what I would like to have, I have attached some anonymized data to show what I'm looking at, so you can reference it...

Spoiler

Date,Ticket_ID,Action
2019/10/08,49011,
2019/10/08,49213,
2019/10/08,49316,
2019/10/08,49670,
2019/10/08,51597,
2019/10/08,51943,
2019/10/08,56289,
2019/10/08,58180,
2019/10/08,60731,
2019/10/09,49011,
2019/10/09,49213,
2019/10/09,49316,
2019/10/09,49670,
2019/10/09,51943,
2019/10/09,56289,
2019/10/09,58158,
2019/10/09,58180,
2019/10/09,60731,
2019/10/09,61324,
2019/10/09,65748,
2019/10/09,65824,
2019/10/09,66511,
2019/10/10,49011,
2019/10/10,49213,
2019/10/10,49316,
2019/10/10,49670,
2019/10/10,51943,
2019/10/10,56289,
2019/10/10,58180,
2019/10/10,60731,
2019/10/10,61324,
2019/10/10,65748,
2019/10/10,65824,
2019/10/11,49011,
2019/10/11,49213,
2019/10/11,49316,
2019/10/11,49670,
2019/10/11,51943,
2019/10/11,56289,
2019/10/11,58180,
2019/10/11,60731,
2019/10/11,61324,
2019/10/11,65748,
2019/10/11,65824,
2019/10/14,49011,
2019/10/14,49213,
2019/10/14,49316,
2019/10/14,49670,
2019/10/14,51943,
2019/10/14,56289,
2019/10/14,58180,
2019/10/14,60731,
2019/10/14,61324,
2019/10/14,65748,
2019/10/14,65824,
2019/10/15,49011,
2019/10/15,49213,
2019/10/15,49316,
2019/10/15,49670,
2019/10/15,51943,
2019/10/15,56289,
2019/10/15,58180,
2019/10/15,60731,
2019/10/15,61324,
2019/10/15,65824,
2019/10/16,49011,
2019/10/16,49213,
2019/10/16,49316,
2019/10/16,49670,
2019/10/16,51943,
2019/10/16,56289,
2019/10/16,58180,
2019/10/16,60731,
2019/10/16,65824,
2019/10/17,49011,
2019/10/17,49213,
2019/10/17,49670,
2019/10/17,49758,
2019/10/17,50427,
2019/10/17,50949,
2019/10/17,51943,
2019/10/17,56289,
2019/10/17,58180,
2019/10/17,60731,
2019/10/17,65824,
2019/10/18,49011,
2019/10/18,49213,
2019/10/18,49670,
2019/10/18,49758,
2019/10/18,50427,
2019/10/18,50949,
2019/10/18,51943,
2019/10/18,56289,
2019/10/18,58180,
2019/10/18,59956,
2019/10/18,60593,
2019/10/18,60731,
2019/10/18,60823,
2019/10/18,65824,

What I would like to see now, is:

- In case a ticket ID was not present the day before: Add "IN" (or something similar) in the "Action" column

- In case a ticket ID was not present the day after: Add "OUT" in the "Action" column

- In other cases: Add nothing to the "Action" column