cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wwcnd Member
Member

Show created and closed in one chart

hi 

 

So in our table we can some cases , we have a column that shows case created date and case closed date.

How is it possible to get a view like this, when the closed and open dates are on the same row and not controlled by action.

 

openclosed.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Show created and closed in one chart

Hi @wwcnd,

 

Based on the information provided, I did the following

 

1. After loading data , go to Edit Query and Select Columns Created and Closed Date and unpivot these columns

2. This will create a row for each case nr, a row for Created Date and another row for closed Date.

3. Rename the column Attribute as Type. Rename the column value as Date

4. Created a Date dimension table called as Calendar and linked the date from this table to Date table of source data at step 3.

5. Created a measure CountCases = DISTINCTCOUNT([Case nr])

6. Created a measure for 
    CreatedCases = Calculate([CountCases],CaseData[Type] ="Case created date")

7. Similarly

      ClosedCases = Calculate([CountCases],CaseData[Type] ="Case Closed date")

8. Added columns to the calendar table

     MonthNumber = Month([Date])

     Year = Year([Date])

     YearMonth = Format([Date],"mmm") & "-" & Format([Date],"yy")

 

9. Now plotted a clustered column chart. using YearMonth as Axis and  CreatedCases and ClosedCases as Values.

 

If this works for you please accept this as solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
14 REPLIES 14
bsas Member
Member

Re: Show created and closed in one chart

Hi @wwcnd,

 

Did you try to use "Clustered column chart"? In value put 2 columns with your data for cases.

wwcnd Member
Member

Re: Show created and closed in one chart

That is not the problem : )

The problem is to show both closed and created cases in a clustered chart , e.g. number of closed cases in a given month and number of created cases in a given month

 

when the data looks like this in the table 

 

Case nr | Case Type | Case created date | Case Closed date

123       | High         | 01-05-2017            | 15-05-2017

456       | High         | 22-06-2017            | 15-09-2017

bsas Member
Member

Re: Show created and closed in one chart

@wwcnd

 

Go to format in mentioned visual -> data labels = on. You'll se numbers of CAse nr

wwcnd Member
Member

Re: Show created and closed in one chart

i think it will work by counting the created and closed dates and use a date dimension
Highlighted
bsas Member
Member

Re: Show created and closed in one chart

@wwcnd

 

you can just use "count" when you click on your value.

 

Untitled.png

Super User
Super User

Re: Show created and closed in one chart

Hi @wwcnd,

 

Based on the information provided, I did the following

 

1. After loading data , go to Edit Query and Select Columns Created and Closed Date and unpivot these columns

2. This will create a row for each case nr, a row for Created Date and another row for closed Date.

3. Rename the column Attribute as Type. Rename the column value as Date

4. Created a Date dimension table called as Calendar and linked the date from this table to Date table of source data at step 3.

5. Created a measure CountCases = DISTINCTCOUNT([Case nr])

6. Created a measure for 
    CreatedCases = Calculate([CountCases],CaseData[Type] ="Case created date")

7. Similarly

      ClosedCases = Calculate([CountCases],CaseData[Type] ="Case Closed date")

8. Added columns to the calendar table

     MonthNumber = Month([Date])

     Year = Year([Date])

     YearMonth = Format([Date],"mmm") & "-" & Format([Date],"yy")

 

9. Now plotted a clustered column chart. using YearMonth as Axis and  CreatedCases and ClosedCases as Values.

 

If this works for you please accept this as solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
wwcnd Member
Member

Re: Show created and closed in one chart

i will give it a try within the next couple of days and get back to you thanks
wwcnd Member
Member

Re: Show created and closed in one chart

Works perfect, thank you very much for the detailed description
mpowerbi Frequent Visitor
Frequent Visitor

Re: Show created and closed in one chart

Sorry I did not understand step 4. Could you explain better the structure of the date dimension  table (Calendar table) and to what it has to be linked. 

 

Thank you