Scatter with columns from related tables with many to many relationship

[mwats20]

Hello,

I am trying to do a scatter with two columns from related tables, one column as X axis and another from Y axis that is in the related table.  They are related with a many to many relationship by their date column, which I would like to use as the detail.  The X axis column will be spliced down to just one value per date, while the Y axis column will also be spliced down but will end up being a sum of values per date.  I would like to show color of the data points by year.  Sample data in the tables below and example graph below.  I have attempted calculated tables, but have not been able to get anything to work.

Example of Table 1 - this would be spliced down to 1 city.

Table 1
MeasureDate	City	Temp
1/1/2018	Chicago	35
1/17/2018	Chicago	40
2/25/2018	Chicago	25
6/5/2018	Chicago	80
10/5/2018	Chicago	70
12/25/2018	Chicago	40
1/2/2019	Chicago	30
2/23/2019	Chicago	5
3/5/2019	Chicago	45
3/17/2019	Chicago	47
7/31/2019	Chicago	90
8/6/2019	Chicago	95
8/12/2019	Chicago	93
11/15/2019	Chicago	40
1/5/2020	Chicago	20
3/5/2020	Chicago	30
4/10/2020	Chicago	45
1/1/2018	Houston	45
1/17/2018	Houston	50
2/25/2018	Houston	35
6/5/2018	Houston	90
10/5/2018	Houston	80
12/25/2018	Houston	50
1/2/2019	Houston	40
2/23/2019	Houston	15
3/5/2019	Houston	55
3/17/2019	Houston	57
7/31/2019	Houston	100
8/6/2019	Houston	105
8/12/2019	Houston	103
11/15/2019	Houston	50
1/5/2020	Houston	30
3/5/2020	Houston	40
4/10/2020	Houston	55

Example Table 2 - this would be spliced down to one company that has multiple values per date.  All those values would be summed.  (In actuality my data has more than one category that will be spliced, so there is really say Company, State, County, Customertype, etc).

Table 2
NotedDate	Company	Volume
1/1/2018	A	4888.8
1/17/2018	A	4000
2/25/2018	A	6000
6/5/2018	A	3600
10/5/2018	A	3200
12/25/2018	A	4200
1/2/2019	A	5400
2/23/2019	A	6800
3/5/2019	A	3600
3/17/2019	A	3400
7/31/2019	A	4000
8/6/2019	A	4200
8/12/2019	A	4120
11/15/2019	A	4080
1/5/2020	A	6200
3/5/2020	A	5480
4/10/2020	A	3800
1/1/2018	A	7333.2
1/17/2018	A	6000
2/25/2018	A	9000
6/5/2018	A	5400
10/5/2018	A	4800
12/25/2018	A	6300
1/2/2019	A	8100
2/23/2019	A	10200
3/5/2019	A	5400
3/17/2019	A	5100
7/31/2019	A	6000
8/6/2019	A	6300
8/12/2019	A	6180
11/15/2019	A	6120
1/5/2020	A	9300
3/5/2020	A	8220
4/10/2020	A	5700
1/1/2018	B	6505.44
1/17/2018	B	5350
2/25/2018	B	7950
6/5/2018	B	4830
10/5/2018	B	4310
12/25/2018	B	5610
1/2/2019	B	7170
2/23/2019	B	8990
3/5/2019	B	4830
3/17/2019	B	4570
7/31/2019	B	5350
8/6/2019	B	5610
8/12/2019	B	5506
11/15/2019	B	5454
1/5/2020	B	8210
3/5/2020	B	7274
4/10/2020	B	5090
1/1/2018	B	9683.16
1/17/2018	B	7950
2/25/2018	B	11850
6/5/2018	B	7170
10/5/2018	B	6390
12/25/2018	B	8340
1/2/2019	B	10680
2/23/2019	B	13410
3/5/2019	B	7170
3/17/2019	B	6780
7/31/2019	B	7950
8/6/2019	B	8340
8/12/2019	B	8184
11/15/2019	B	8106
1/5/2020	B	12240
3/5/2020	B	10836
4/10/2020	B	7560

The data that I want to graph (temp against volume with date as the detail and year as the color in the legend) would be this:

Date	Year	Temp	Volume
1/1/2018	2018	35	12222
1/17/2018	2018	40	10000
2/25/2018	2018	25	15000
6/5/2018	2018	80	9000
10/5/2018	2018	70	8000
12/25/2018	2018	40	10500
1/2/2019	2019	30	13500
2/23/2019	2019	5	17000
3/5/2019	2019	45	9000
3/17/2019	2019	47	8500
7/31/2019	2019	90	10000
8/6/2019	2019	95	10500
8/12/2019	2019	93	10300
11/15/2019	2019	40	10200
1/5/2020	2020	20	15500
3/5/2020	2020	30	13700
4/10/2020	2020	45	9500

Thank you for your help!

Megan

[amitchandak · Accepted solution]

@mwats20 , create a common date table. Join that with both the table. You can have year , month day there.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[mwats20]

This did exactly what I needed it to do, thank you so much!

[mwats20]

Here is a pic of the graph I'm trying to create, which I couldnt get into my original post.