How to build this chart with inbound ticket & outbound ticket on same bar

[Zemotard]

Dear all,

I would like to create a chart showing the Inbound tickets & outbound tickets by month.

In my example, I have 4 records

Open Date	Closing Date
15/09/2024 08:42	15/09/2024 08:33
15/09/2024 08:16	15/10/2024 08:33
15/10/2024 07:32	15/10/2024 08:33
15/10/2024 08:10	21/10/2024 08:33

I would like a chart with open date & closing date on the same bar.

Outbound tickets should be negative

The months sould be in X axis, and qty in Y  then with this recordset,  I should have 2 tickets opened in september, 2 in october.

And 1 ticket closed in september and 3 in october.

open        2                      2

        0--------------------------0

 closed   -1                      -3

             Sept             Oct

Could you help me ?

Thanks

[OktayPamuk80]

Hi,

You can create a calendar in Power Query, match it with the existing table by creating a new table and create flags in order to sum it. It is harder to explain, so I created an samle file for you:

https://drive.google.com/drive/folders/1-1hilkkcjEjc451A7XMAVJtU701LqY3W?usp=sharing

Depending on your source data, you can adjust it or create it more dynamically.

Regards,
Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[Lucas_LP · Accepted solution]

Hi Zemotard!

My suggestion is to:
First, select both columns in PQuery and click unpivot them, leaving you with this table:
Attribute          Date

Open Date	9/9/2024
Closing Date	9/9/2024
Open Date	9/9/2024
Closing Date	10/10/2024
Open Date	10/10/2024
Closing Date	10/10/2024
Open Date	10/10/2024
Closing Date	10/10/2024

Second, close and apply query, go to "Table View", create new column (this column could also be done in PQuery if you prefer). Use this DAX formula:

Value =

IF('Table (2)'[Attribute]="Open Date", 1, -1).

Finally, set the visual as follows:

You can change "Closing Date" and "Open Date" by renaming the columns before unpivoting them, or by using a find replace in PQuery for the attribute column. Or creating another column.

 Is this what you needed?

[Zemotard]

Thanks Lucas_LP, I use your method, and this works perfectly !!

Thanks again  🙂