Compare in a bar graph

[JohnnyWind]

Dear all ,

I'm quite new in PowerBI, and I guess this question is easy... 

I have a table like this one :

Incident ID	Created date	Closed date
I1	01/01/2020	02/03/2020
I2	01/01/2020	12/01/2020

I would like to create a graph showing the number of created incidents and closed incidents by month. 

The only thing I managed to create now is two tables like this :

But I need the bar graph comparing open vs closed by month.

I appreciate you help.

[BA_Pete · Accepted solution]

Hi @JohnnyWind ,

You will need a separate calendar table in your data model, this is where you will get the [Month] field from for your chart axis. The easiest way to achieve this is to create a new table in the Data view and paste the following in the formula bar:

Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Month", FORMAT([Date], "mmm")
)

Once you have that, you can create relationships as follows:

1) Calendar[Date] (one) to YourTable[Created Date] (many) - ACTIVE relationship

2) Calendar[Date] (one) to YourTable[Closed Date] (many) - INACTIVE relationship

You would then create the following measures:

_noofCreated = DISTINCTCOUNT(aTable[Incident ID])

_noofClosed = 
CALCULATE(
    DISTINCTCOUNT(aTable[Incident ID]),
    USERELATIONSHIP(aTable[Closed date], Calendar[Date])
)

Notice that the _noofClosed measure is different in that it uses CALCULATE and USERELATIONSHIP - this forces Power BI to use your inactive relationship between the calendar table and your table for that measure.

This give me the following output:

Pete

[JohnnyWind]

Really good quality answers.

I created the table "calendar" and followed your steps.

Thanks for your help guys.

Joan

[rogletree]

Hi,

On the "Report" page, from the Visualisations pane you can choose "Clustered column chart". Then for the "Axis", put in your field that has the months. For the "values" field, drag over both your fields for created incidents and closed incidents. This will show the months along the x-axis and the number of incidents along the y-axis, with two bars for each months.

You may also want to do a line and clustered column chart which will show the same thing, but with one of the y-axis values being shown by a line graph. Just to mix things up a little bit if you want.

[JohnnyWind]

Hi Rogletree,

The problem is that I have to month columns, one with open date and the other one with the closing date.

Best regards,

[v-easonf-msft · Accepted solution]

Hi, @JohnnyWind

As mentioned by @BA_Pete ,you can create a INACTIVE relationship.

I make a sample file.Hope it can help you ,If it doesn't meet your requirement, please share more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rogletree]

@JohnnyWind you could try creating a lookup table that contains only the months, then create a relationship from that table to each of your incidents opened/incidents closed tables.

In power query, duplicate one of the tables. Then in your new table, remove all columns except for the months column. Then in that column, remove duplicate rows (sounds like there shouldn't be any duplicates anyways but just to be safe). Then close & apply.

Then go to "Model" view and in there you can create the relationships by dragging/dropping the fields on top of each other and Power BI will create the relationship. In the lookup table, click and hold the months field and drag it to the months field for the incidents created table. Then do the same thing with the other table.

Now you should be able to do the graph.

[BA_Pete · Accepted solution]

Hi @JohnnyWind ,

You will need a separate calendar table in your data model, this is where you will get the [Month] field from for your chart axis. The easiest way to achieve this is to create a new table in the Data view and paste the following in the formula bar:

Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Month", FORMAT([Date], "mmm")
)

Once you have that, you can create relationships as follows:

1) Calendar[Date] (one) to YourTable[Created Date] (many) - ACTIVE relationship

2) Calendar[Date] (one) to YourTable[Closed Date] (many) - INACTIVE relationship

You would then create the following measures:

_noofCreated = DISTINCTCOUNT(aTable[Incident ID])

_noofClosed = 
CALCULATE(
    DISTINCTCOUNT(aTable[Incident ID]),
    USERELATIONSHIP(aTable[Closed date], Calendar[Date])
)

Notice that the _noofClosed measure is different in that it uses CALCULATE and USERELATIONSHIP - this forces Power BI to use your inactive relationship between the calendar table and your table for that measure.

This give me the following output:

Pete

[AllisonKennedy]

@JohnnyWind 

I agree with the answer that @BA_Pete has provided - he has explained very well what to do so that you can compare two dates in the same visual. 

Do you already have a Calendar or DimDate in your data model? If not, it might help you to research a bit about what a DimDate table is and why you need one. You can start here https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Then please let us know if you still have any questions after trying the solution from @BA_Pete . If his solution doesn't work, please explain what you have tried and what errors or incorrect results you're still getting so we can help you out more. 

Cheers!