Forum Discussion
Clustered column chart - Combined Chart
Hi Masters,
How to create a clustered column chart comparing the amount of Incident Open x amount of Incident Closed by month?
The expected result should be like this:
Table sample:
| Incident Number | Date Recorded | Date Closed |
| 2 | 04/01/20 | 04/15/20 |
| 3 | 04/02/20 | 04/16/20 |
| 4 | 04/03/20 | 04/17/20 |
| 5 | 04/04/20 | 04/18/20 |
| 6 | 04/05/20 | 04/19/20 |
| 7 | 04/06/20 | 04/20/20 |
| 8 | 04/07/20 | 04/21/20 |
| 9 | 04/08/20 | 04/22/20 |
| 10 | 01/03/20 | 02/11/20 |
| 11 | 01/04/20 | 02/12/20 |
| 12 | 01/05/20 | 02/13/20 |
| 13 | 01/06/20 | 02/14/20 |
| 14 | 01/07/20 | 02/15/20 |
| 15 | 01/08/20 | 02/16/20 |
| 16 | 02/10/20 | 03/20/20 |
| 17 | 02/11/20 | 03/21/20 |
| 18 | 02/12/20 | 03/22/20 |
| 19 | 02/13/20 | 03/23/20 |
| 20 | 02/14/20 | 03/24/20 |
| 21 | 02/15/20 | 03/25/20 |
| 22 | 02/16/20 | 03/26/20 |
| 23 | 02/17/20 | 03/27/20 |
| 24 | 02/18/20 | 03/28/20 |
| 25 | 03/18/20 | 04/01/20 |
| 26 | 03/19/20 | 04/02/20 |
| 27 | 03/20/20 | 04/03/20 |
| 28 | 03/21/20 | 04/04/20 |
| 29 | 03/22/20 | 04/05/20 |
| 30 | 03/23/20 | 04/06/20 |
| 31 | 03/24/20 | 04/07/20 |
Thanks in advance!
If I got it correctly. You need to create a common Date dimension and join both dates with it. One active and one Inactive. And then use userelation to activate the other.
Refer to My blog that will help you : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
4 Replies
If I got it correctly. You need to create a common Date dimension and join both dates with it. One active and one Inactive. And then use userelation to activate the other.
Refer to My blog that will help you : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Thanks amitchandak! With this example I could develop my chart.
Another solution was duplicate the table leaving just Closed column in there and link it with the Calendar table. Not very clever but works 🙂
Thank you.
Hi Erikestrela ,
If I understood how total open and total closed calculated correctly, you can create a Date table first and then create the following measures:
Total open = VAR _date = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Table'[Incident Number] ), FILTER ( ALLSELECTED ( 'Table' ), _date >= 'Table'[Date Recorded] && _date <= 'Table'[Date Closed] ) )Total Closed = VAR _date = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Table'[Incident Number] ), FILTER ( ALLSELECTED ( 'Table' ), _date < 'Table'[Date Recorded] || _date > 'Table'[Date Closed] ) )Here is the demo based on my understanding, please try it:
If my understanding has something wrong, please let me know and you can share more details like how they calculate for further discussion.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thank you v-yingil!
With your example I could have more insights of how to deal with it.
