cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Erikestrela
Frequent Visitor

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:

 

OpenVSClosed.png

 

 

 

Table sample:

 

Incident NumberDate Recorded Date Closed
204/01/2004/15/20
304/02/2004/16/20
404/03/2004/17/20
504/04/2004/18/20
604/05/2004/19/20
704/06/2004/20/20
804/07/2004/21/20
904/08/2004/22/20
1001/03/2002/11/20
1101/04/2002/12/20
1201/05/2002/13/20
1301/06/2002/14/20
1401/07/2002/15/20
1501/08/2002/16/20
1602/10/2003/20/20
1702/11/2003/21/20
1802/12/2003/22/20
1902/13/2003/23/20
2002/14/2003/24/20
2102/15/2003/25/20
2202/16/2003/26/20
2302/17/2003/27/20
2402/18/2003/28/20
2503/18/2004/01/20
2603/19/2004/02/20
2703/20/2004/03/20
2803/21/2004/04/20
2903/22/2004/05/20
3003/23/2004/06/20
3103/24/2004/07/20

 

Thanks in advance!

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
Community Support

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.

Thank you v-yingil! 

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

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors