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 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!
Solved! Go to Solution.
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-tr...
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 Li
If 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.
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-tr...
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.
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |