New update: Dual Axis line chart assistance

[nosaj03]

Hello all! I recently updated to the latest release and I am overjoyed that they now have dual axis line charts. 

I have data by day from the beginning of 2019 to current. I would like to use the dual axis functionality to show 2019 vs 2020. 

No I know I was able to do this previously without the need of another axis but I also want to bring in the day of the year so they line up accordingly. 

How do I go about doing this?

[amitchandak]

@nosaj03 ,

Please refer :https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-2020-feature-summary/#_Dual_axis

[nosaj03]

@amitchandak thank you for the link. I read through it and it does tell me how to use the Y2 field but it only pertains to different data sets. 

My data is in a single column sorted by date and what Im looking to do is show a line for each relevant day Year over year similar to below which was done in Excel. 

 

[v-lionel-msft]

Hi @nosaj03 ,

2019 = 
CALCULATE(
    SUM(Sheet3[Vale]),
    FILTER(
        Sheet3,
        Sheet3[Date].[Year] = 2019
    )
)

2020 = 
CALCULATE(
    SUM(Sheet3[Vale]),
    FILTER(
        Sheet3,
        Sheet3[Date].[Year] = 2020
    )
)

Use "Month" and "Day" to create a common axis and compare the data for each day in different years.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nosaj03]

@v-lionel-msft this is perfect. The only issue is that its now looking at the specific dates which can compare a Sunday to Monday depending on the differences between the years. How can I compare based on day of the week. For example 1/2/2020 was the first Thursday in January but the first Thursday in January 2019 was 1/3/2019. I dont want to compare 1/2/2020 to 1/2/2019 because they fall on different days. I'm trying to compare relative days year over year. 

[v-lionel-msft]

Hi @nosaj03 ,

How about creating such a common axis?

//Calculated column
Week = 
VAR week_num = WEEKNUM([Date],2)
VAR week_day = WEEKDAY([Date],2)
RETURN
week_num & "-" & week_day 

You can add this calculated column to the line chart as the X axis.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@nosaj03 

You have to use trailing year measure

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))