Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
CheenuSing
Community Champion
Community Champion

Hi @Anonymous,

 

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!

View solution in original post

14 REPLIES 14
CheenuSing
Community Champion
Community Champion

Hi @Anonymous,

 

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!
Anonymous
Not applicable

Hi.

 

This would work for me but my only concern is the following.  I connect directly to a salesforce object and I refresh the data from there.  How the unpivoting and the renaming of the columns work?  Will i have to do it everytime? 

 

Thank you,

Angel

Hi @Anonymous

 

You don't have to do every  time. All the scripts in power query will get executed everytime you refresh.

 

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!
Anonymous
Not applicable

Hi.

 

This would work for me but my only concern is the following.  I connect directly to a salesforce object and I refresh the data from there.  How the unpivoting and the renaming of the columns work?  Will i have to do it everytime? 

 

Thank you,

Angel

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

Anonymous
Not applicable

Hi 

 

Please have a look here on how to create a date dimension

https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/

or here 

http://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

 

you then need to link the Date column from the date dimension with the date column the other table

 

 

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

Anonymous
Not applicable

Works perfect, thank you very much for the detailed description
Anonymous
Not applicable

i will give it a try within the next couple of days and get back to you thanks
bsas
Post Patron
Post Patron

Hi @Anonymous,

 

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

@Anonymous

 

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

 

Untitled.png

@Anonymous

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.