cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
_n_MarianLein Frequent Visitor
Frequent Visitor

Flow-In / Flow-Out chart

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!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Flow-In / Flow-Out chart

Hi @_n_MarianLein ,

 

To create two measures as below.

count = CALCULATE(DISTINCTCOUNT('Table'[Ticket ICapture.PNG
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 )
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
_n_MarianLein Frequent Visitor
Frequent Visitor

Re: Flow-In / Flow-Out chart

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
daily_ticket_difference.png"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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors