[Measure1 by Date1] and [Measure2 by Date2] on the same line chart

[Anonymous]

Hi there

I have trade data: mass, departure date and arrival date. I can plot exports mass by departure time (exports) and mass vs arrival date (imports) on separate charts, but I need to plot them on the same chart so that I can compare them easily. Effectively I want to plot two things on the same chart: (1) Imports by Arrival and (2) Exports by Departure. When I put them together on the same chart, one of them looks good but the other one looks very small (which that is depends on the order of the time variables I put on the axis: departure then arrival, or arrival then departure). Pictures are below and sample data too. Does anyone know how to overcome this?

Thanks!

Need to plot Exports by Departure, and Imports by Arrival, on the same chart

Mass	Departure (UTC)	Arrival (UTC)
69360.72	19 Aug 2018 05:43:25	29 Aug 2018 22:05:53
72032.40	15 Aug 2018 15:52:47	28 Aug 2018 01:55:13
77132.54	13 Aug 2018 06:34:13	25 Aug 2018 05:11:12
80747.59	10 Aug 2018 04:56:34	21 Aug 2018 00:09:04
67683.45	08 Aug 2018 01:15:07	24 Aug 2018 21:57:07
63708.39	03 Aug 2018 04:03:53	18 Aug 2018 02:03:12
78192.10	01 Aug 2018 03:03:38	11 Aug 2018 22:24:25
81134.50	28 Jul 2018 13:22:14	06 Aug 2018 23:30:05
73741.50	24 Jul 2018 13:54:27	03 Aug 2018 01:03:27
77804.04	21 Jul 2018 10:09:47	05 Aug 2018 23:54:41
66500.09	18 Jul 2018 05:43:20	31 Jul 2018 03:36:01
78608.73	15 Jul 2018 09:58:07	26 Jul 2018 10:03:03
66411.13	12 Jul 2018 06:10:15	26 Jul 2018 03:00:45
79039.00	10 Jul 2018 11:23:34	21 Jul 2018 05:05:15
73400.72	06 Jul 2018 14:06:05	17 Jul 2018 02:41:37
	06 Jul 2018 14:06:05	17 Jul 2018 02:41:37
78192.10	04 Jul 2018 19:48:11	16 Jul 2018 00:07:00
78856.58	01 Jul 2018 22:09:48	12 Jul 2018 02:24:57
70240.18	26 Jun 2018 20:15:01	05 Jul 2018 23:00:48
78123.07	25 Jun 2018 18:02:48	07 Jul 2018 03:45:54
77591.00	21 Jun 2018 21:28:23	02 Jul 2018 22:49:53
80161.50	18 Jun 2018 05:52:49	30 Jun 2018 00:49:18
67358.50	15 Jun 2018 21:09:56	30 Jun 2018 23:00:26
78429.06	11 Jun 2018 21:51:47	23 Jun 2018 00:09:55
78345.43	08 Jun 2018 05:42:45	18 Jun 2018 01:54:13
66031.00	03 Jun 2018 22:39:17	21 Jun 2018 01:51:28
76804.00	01 Jun 2018 05:17:36	11 Jun 2018 07:03:00
80148.00	29 May 2018 04:22:55	06 Jun 2018 22:58:52
77931.63	26 May 2018 01:43:00	07 Jun 2018 23:59:01
76704.00	23 May 2018 01:56:34	02 Jun 2018 01:30:05
65953.00	19 May 2018 17:09:19	03 Jun 2018 02:06:04
78429.06	16 May 2018 21:29:50	30 May 2018 22:28:20
76570.95	12 May 2018 22:00:39	23 May 2018 03:41:31
73760.25	11 May 2018 13:23:04	22 May 2018 06:05:00
76856.00	07 May 2018 05:22:09	17 May 2018 22:44:39
78160.00	04 May 2018 06:36:44	24 May 2018 08:34:52

Exports (10d MAv) = calculate(sum(Sheet1[Mass]),DATESINPERIOD(Sheet1[Departure (UTC)],LASTDATE(Sheet1[Departure (UTC)]),-10,DAY))*365.25/12/10

Imports (10d MAv) = calculate(sum(Sheet1[Mass]),DATESINPERIOD(Sheet1[Arrival (UTC)],LASTDATE(Sheet1[Arrival (UTC)]),-10,DAY))*365.25/12/10

[v-piga-msft]

Hi @Anonymous,

Please follow the steps below.

1. Firstly , you should create an index column in Query Editor.

2. Then try the formula below to create the detail table and create the relationship between with the original table.

Detail = 
VAR temp =
    SELECTCOLUMNS (
        'Sheet1',
        "Index", [Index],
        "StartDate", [Departure],
        "EndDate", [Arrival]
    )
VAR vCalendar =
    CALENDAR ( MINX ( temp, [StartDate] ), MAXX ( temp, [EndDate] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( temp, vCalendar ),
            [StartDate] <= [Date]
                && [EndDate] >= [Date]
        ),
        "Index", [Index],
        "StartDate", [StartDate],
        "EndDate", [EndDate],
        "Detail", [Date]
    )

3. Then you could create the line chart with the Detail column as shared Axis and the two measure you created as values.

More details, you could refer to the attachment.

Best Regards,

Cherry

[Anonymous]

@v-piga-msft

Thank you for your response! I have been looking at your solution and trying to learn from it. Observing your results, it does not seem to do what I would like because the green/red lines on the combined chart are different to the green/red lines on the individual charts. What I would like is to superimpose the green/red lines on one chart without changing them... let me know if you have other ideas please

[Anonymous · Accepted solution]

I resolved it using this idea: https://community.powerbi.com/t5/Desktop/Pull-data-in-different-columns-of-one-table-into-another-ta...