How to include closed items in the stacked column chart?

[jbrolland]

Hello

I have the below data

STATUS	Opened Year and Month	Closed Year and Month
DONE	2021-1	2021-1
DONE	2021-1	2021-2
ONGOING	2021-1
DONE	2021-1	2021-2
DONE	2021-1	2021-1
DONE	2021-1	2021-3
ONGOING	2021-1
ONGOING	2021-1
DONE	2021-1	2021-2
DONE	2021-1	2021-3
DONE	2021-1	2021-1
DONE	2021-1	2021-2
ONGOING	2021-1
DONE	2021-1	2021-2
DONE	2021-1	2021-1
DONE	2021-2	2021-3
DONE	2021-2	2021-3
DONE	2021-2	2021-2
DONE	2021-2	2021-4
ONGOING	2021-2
DONE	2021-2	2021-2
ONGOING	2021-2
DONE	2021-2	2021-3
DONE	2021-2	2021-3
DONE	2021-2	2021-3
DONE	2021-2	2021-3
DONE	2021-2	2021-3
DONE	2021-1	2021-2
DONE	2021-1	2021-2
DONE	2021-2	2021-1
ONGOING	2021-1
DONE	2021-2	2021-2
DONE	2021-2	2021-3
DONE	2021-2	2021-3
DONE	2021-2	2021-4
DONE	2021-3	2021-3
DONE	2021-3	2021-4
DONE	2021-2	2021-3
DONE	2021-3	2021-4
DONE	2021-3	2021-4
ONGOING	2021-2
ONGOING	2021-3
DONE	2021-3	2021-3
DONE	2021-3	2021-3
ONGOING	2021-3
ONGOING	2021-3
DONE	2021-3	2021-1
DONE	2021-3	2021-1
ONGOING	2021-3
ONGOING	2021-3
ONGOING	2021-3
ONGOING	2021-3
ONGOING	2021-3
NOT FIXED	2021-3
ONGOING	2021-4
ONGOING	2021-4
DONE	2021-4	2021-4
ONGOING	2021-4
ONGOING	2021-4
ONGOING	2021-4
ONGOING	2021-4

I am trying:

• to have columns which represent the number of open incident per month (this I can actually do it, cf below)
• to insert within those columns the number of closed incident per month (this I cannot, I am always ending with the wrong number of DONE and ONGOING)

How can I achieve this? I have tried many things but I am clueless

[v-yetao1-msft]

Hi @jbrolland ，

Please correct me if I wrongly understood your question.

For STATUS=”DONE” ,there are two dates , one is Opened Year and Month and other is Closed Year and Month . So if you use different date to count ,you will get different results . If you put different dates and different status in the same visual, the displayed results will be more complicated .My suggestion is creating two visuals to display the counts of two dates .

I create measures to count different status .

ONGING = CALCULATE( COUNTA('Table'[STATUS]),FILTER('Table','Table'[STATUS]="ONGOING" ))

NOT FIXED = CALCULATE( COUNTA('Table'[STATUS]),FILTER('Table', 'Table'[STATUS]="NOT FIXED"))

DONE = CALCULATE( COUNTA('Table'[STATUS]),FILTER('Table','Table'[STATUS]="DONE"))

Then create two clustered column chart visuals ,like this

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jbrolland]

Thanks @Ashish_Mathur  for taking time to answer my question.

Opened Year and Month and Closed Year and Month having matching values, isn't it possible to put everything on the same graph?

PS: How did you format to obtain YYYY-Month format on the X axis ?

[Ashish_Mathur]

You are welcome.  It is possible.  Please provide the information i requested for in my previous message.

[jbrolland]

Anyone?

[Ashish_Mathur]

Hi,

I'd like ot help but i think your data is incomplete.  Shouldn't there be a Customer or Vedor or Project column as well in your dataset?

[jbrolland]

Sorry I missed your answer.

Well not really cause this is incidents cases so except incident description (which is different for every single row) there is not much to add.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  Using the Query Editor, i have been able to transform data to the shape and form you see in the image below.  You should now be able to build your desired visual.