Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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
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
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
Hi @Anonymous,
Did you try to use "Clustered column chart"? In value put 2 columns with your data for cases.
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
you can just use "count" when you click on your value.
@Anonymous
Go to format in mentioned visual -> data labels = on. You'll se numbers of CAse nr
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |