How to create a shared axis(date) for two date columns

[Anonymous]

Hello all,

I have two date columns one is CreatedDate, the other is ClosedDate. 

You can think like it is a incident ticket, service ticket etc.

Format is as below:

I want to see them in a line graph or bar graph but in shared axis.

Same question is asked before but there are shared files in topic which are not available right now.

https://community.powerbi.com/t5/Desktop/quot-Shared-Axis-quot-for-two-date-columns-on-Clustered-Lin...

I tag them just in case they are around:

@MWitkin 

@Ashish_Mathur 

If I had to do it in excel, I would create all year-month-day combination in another sheet, then pivot those two columns to get counts and use vlookup to year-month-day sheet. But creating a new table just for that seems not so elegant also I don't know how to do it in PowerBI as well.

Can you please help?

Thanks for any idea or comment

[jimminy]

Have you been able to find a solution to this? I am having the very same issue

[Ashish_Mathur]

Hi,

Your question is not clear.  Show the exact result in a Table format first.  From there we can build any visual that you want.

[Anonymous]

@Ashish_Mathur 

As a result I need below visual:

I created a toy dataset as below to have above visual:

It is quite easy in excel but "Date" column above includes all dates which I don't have it in PowerBI.

"Created" is a count of created events, "Completed" is same as well.

In PowerBI dataset I don't have this structure. I have "Created" and "Completed" columns as only dates as below:

Each row represent a unique event.

I don't know if it is the only way, but as a result I need above bar chart - all dates should be in same axis and values are count of created-completed events on them-

Actually it is nearly same question that you answered around 3 years ago that I shared the link in topic, but I couldn't follow up since files are not available right now.

Thanks for your help

Greetings,

[Ashish_Mathur]

Hi,

What should appear in the column chart for March 13, 2020?

[Anonymous]

Hello @Ashish_Mathur 

It should be 0 since no event occured on that day.

Thanks

Greetings

[Ashish_Mathur]

Hi,

This is the max i can do.  Download the file from here.

Hope this helps.

[Anonymous]

I assume when you say 2 columns, you mean 2 columns in the data table in your data model. Is that correct? If so, either unpivot the data on load and create 1 column with all the dates and a new column that identifies which date it is.. Alternatively you can create 2 relationships to your data table to the calendars table. The second will be inactive. Then write a measure that calls the inactive relationship for the second measure.

[amitchandak]

You can join with a common date dimension with one active and one inactive relation. You use userelation to activate the inactive one.

Refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...