"Shared Axis" for two date columns on Clustered Line Graph?

[MWitkin]

Hello,

I am hoping someone can suggest a measure to use as a shared axis in a Clustered Line and Bar Graph visual.

I have a table with two date columns, and I would like to display the count of the dates in each column in a bar graph, grouped by month across a multi-year scale. So for example, if there are 4 dates which occur in April '15 in column 1, and 2 dates in April '15 in column 2, then the bars in the graph above April would be valued at 4 and 2, with the X-axis expanding across the full timeline covered in the columns.

Here's a mockup visual of what I am looking for:

I've tried using a few different methods, such as duplicating one of the columns and transforming it into a Start of Month column and using that as the axis, but the numbers are always wrong in at least one of the counts illustrated by the bars.

Any help would be very much appreciated and rewarded with kudos!

[MattAllington]

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. You can read about this in my knowledge base. http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

[brissae]

@MattAllington I am working with something similar to this, I need to compare  received orders vs dispatched orders by month, eachorder has two dates related (arrival and dispatched) what I need to do is something like this:

I tried to unpivot the columns but the result is the same given that the shared axis is the month and I am taking it from either the received or dispatched date. Am I doing something wrong? The result keeps giving the same numbers for both columns each month. I am going to try the alternatively solution but I also would like to resolve it this way.

Thank you! 

[dgwilson]

Did you ever get this working?

I have the same situation with data holding two dates - submitted and completed... I want to show number of units opened vs. number of units closed... the closed never exceeds opened - yet I know in reality a backlog of work is being closed....

What type of chart are you using?

Also, what is meant by unpivot?

- David

[Ashish_Mathur]

Hi @dgwilson,

Share a dataset and show the expected result.

[dgwilson]

Attached is a sample dataset. I'm having difficulity producing the chart required in excel.

What I'm looking to produce is a chart that on the bottom axis has months (or in this case - days in June). On the 3rd June it should show the count of Orders submitted (9 in this case), etc... it should also show against 8 June 10 completed orders, etc.

The answer/solution posted above I think is suggesting that a date table be built (can I get a pointer on how to generate that?) and then some relationships established between columns/tables.

Data as plain text below if it is helpful to experiment with.

Order_Number Submit_Date Complete_Date
WO100000 3-Jun-17 8-Jun-17
WO100001 3-Jun-17 8-Jun-17
WO100002 3-Jun-17 8-Jun-17
WO100003 3-Jun-17 8-Jun-17
WO100004 3-Jun-17 8-Jun-17
WO100005 3-Jun-17 8-Jun-17
WO100006 3-Jun-17 8-Jun-17
WO100007 3-Jun-17 8-Jun-17
WO100008 3-Jun-17 8-Jun-17
WO100009 4-Jun-17 8-Jun-17
WO100010 5-Jun-17 18-Jun-17
WO100011 6-Jun-17 19-Jun-17
WO100012 7-Jun-17 20-Jun-17
WO100013 8-Jun-17 21-Jun-17
WO100014 9-Jun-17 22-Jun-17
WO100015 10-Jun-17 23-Jun-17
WO100016 11-Jun-17 24-Jun-17
WO100017 12-Jun-17 25-Jun-17
WO100018 13-Jun-17 25-Jun-17
WO100019 14-Jun-17 25-Jun-17
WO100020 14-Jun-17 25-Jun-17
WO100021 14-Jun-17 25-Jun-17
WO100022 14-Jun-17 25-Jun-17
WO100023 14-Jun-17 25-Jun-17
WO100024 14-Jun-17 25-Jun-17
WO100025 14-Jun-17 25-Jun-17
WO100026 14-Jun-17  
WO100027 15-Jun-17  
WO100028 16-Jun-17  
WO100029 17-Jun-17  
WO100030 18-Jun-17  
WO100031 19-Jun-17  
WO100032 20-Jun-17  
WO100033 21-Jun-17  

- David 

[dgwilson]

I've built a sample... it's almost working.

Stuck on a circular reference that I'm unable to resolve.

https://sites.google.com/site/dgwilson65/BI%20Sample%20Data%20Data.pbix?attredirects=0&d=1

The generated Date table has two columns that count the number of orders by date. One shows the data, the orher has a circular reference that I'm unable to resolve.

- David

[Ashish_Mathur]

Hi @dgwilson,

See my solution here.

Hope this helps.

[arsalaankhan]

@Ashish_Mathur Thanks for sharing the file. Helped me alot. I hope you won't mind answering one more query? I'm trying to create a measure for running total on the same cluster chart for submit_count and Completed_count. Could you please adivse how can it be done? Thanks in advance.

[Ashish_Mathur]

Hi,

You are welcome.  Please explain the question, show the dataset and the expected result.

[dgwilson]

Very helpful. Thank you.

You added the measure to the main table, not the date table - the otherway around to what I was trying to do.

I've manually checked the data against the graph and it's working as intended.

All I have to do now is implement the example with my actual data... Which is breaking somewhere... lets see if I can solve that.

- David

[Ashish_Mathur]

You are welcome.  Once my solution works fine on your live dataset, please mark my response as Answer.

[dgwilson]

FYI... I've tried to mark your response as the answer - I don't think I can do that when another response in the thread has already been marked as the answer. I have given your response the "Thumbs up".

Again, thank you for your support.

- David

[Ashish_Mathur]

You are most welcome @dgwilson

[dgwilson]

Will do.

For some reason I'm getting a submit_date cout of 1 & ONLY ONE - on data that has 243,295 rows (with everyone having a submit_date). - table view says I have 241,279 distinct values.

Column format is confirmed as Date/Time.

Interesting... Graph says 243,294 = blank on the submit_date

... OK... lightbulb moment. I need to do the data matching on Date - excluding time?

Should I create a new column with just the date? Or can the below be modified to suit?

Submit_Count = COUNT(Sheet1[Submit_Date])

Sorry - while the example does indeed work - I didn't take into account the date/time tipe for the field.

- David

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[dgwilson]

I've updated the example to include date/time.

You can see how it's now broken as compared to before.

https://sites.google.com/site/dgwilson65/BI%20Sample%20Data%20Data%20v2%20with%20time.pbix?attredire...

[Ashish_Mathur]

Hi @dgwilson,

Share the underlying data in an Excel file.

[dgwilson]

Excel data avaialble at:

https://sites.google.com/site/dgwilson65/BI%20Sample%20Date%20Data.xlsx?attredirects=0&d=1

- David

[Ashish_Mathur]

Hi @dgwilson,

You may download my solution from here.

Hope this helps.

[dgwilson]

I had a challenge opening the new pbix file (it's been developed on a newer version of Power BI than what I'm using - and as I'm using a locked corporate laptop I cannot upgrade - I have to wait for the push).

Regardless the file did open after a few complaints.

I think you have set the format of the colums to Date (no time). Is that all you have done? I cannot see otherwise.

Inspired by your assistance/solution - in my real model I've have created two new columns and created a new date only field (i.e. without time). Then I've used that as the basis for the calculations. Everything looks to be working well. Would you agree that this is the approach to use?

The below is what I've produced. You can see the days where backlog is being addressed over the volume of incoming work.

- David