Clustered column chart - Combined Chart

[Erikestrela]

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!

[v-yingjl]

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:

PBIX 

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.

[Erikestrela]

Thank you v-yingil! 

With your example I could have more insights of how to deal with it.

[amitchandak · Accepted solution]

@Erikestrela 

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...

[Erikestrela]

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.