Comparing line graphs with different x-axes (dates) on the same graph

[JoshS]

I am looking to create a single graph that compares 4 different values. The problem I am running into is each value is based off a different date column, which I am looking to combine. Currently, the data exists in 4 seperate graphs as shown below:

I've created dummy data in excel to demonstrate how the data currently exists in Power BI, and the transformation I am hoping to achieve:

Any help in this would be greatly appreciated.  

[JoshS]

Thanks Irwan, that was exactly what I needed.

[Irwan · Accepted solution]

hello @JoshS 

please check if this accomodate your need.

1. create a new date table.

Table 2 = CALENDAR(DATE(2024,6,20),DATE(2024,6,28))

I put minimum date as 20-Jun-2024 and maximum date as 28-Jun-2024.

2. create calculated column for average stage 1

Average Work Days in Stage 1 = AVERAGEX(FILTER('Table','Table'[Stage 1 Completion]='Table 2'[Date]),'Table'[Days in Stage 1])

3. create calculated column for average stage 2

Average Work Days in Stage 2 = AVERAGEX(FILTER('Table','Table'[Stage 2 Completion]='Table 2'[Date]),'Table'[Days in Stage 2])

4. create calculated column for average stage 3

Average Work Days in Stage 3 = AVERAGEX(FILTER('Table','Table'[Stage 3 Completion]='Table 2'[Date]),'Table'[Days in Stage 3])

5. create calculated column for average production

Average Work Days in Production = AVERAGEX(FILTER('Table','Table'[Stage 3 Completion]='Table 2'[Date]),'Table'[Work Days in Production])

so here are what i found weird in your sample data: 

1. in your desired table at stage 2, the average on 25-Jun-2024 is 2,5. However, if I looked at your power bi data, on 25-Jun-2024, the data are 3 and 1. The average should be 2.

2. From what i see, the Average Work Days in Production is taking same date as Stage 3 Completion.

Hope this will help you.

Thank you.