Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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-Line/td-p/25298

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

Need Help
Show and Tell
Tips and Tricks

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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,

       

       

       

       

       

       

       

       

       

       

  • jimminy's avatar
    jimminy
    Frequent Visitor
    4 years ago

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