Forum Discussion

Kishore_Sr4's avatar
Kishore_Sr4
Frequent Visitor
4 years ago
Solved

Combine two different year into one

Hello,

 

I have an Year column which contains 2017, 2018, 2019, 2020, 2021...

 

Now Can i combine 2017, 2018 as 2017 vs 2018 and 2018, 2019 as 2018 vs 2019 and 2019, 2020 as 2019 vs 2020 and 2020, 2021 as 2020 vs 2021, Like the below image

any solution for this, Is it possible!?? 

 

Thanks in Advance!!!

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Kishore_Sr4 

    Plese check my attached pbix file.

    You could try adding custom columns that combines the next/previous year's numbers with the current year's numbers and then combine the query results.

    First Table:

    Next Year

    =[Year]+1

    Combo Year 

    =Text.Combine({Text.From([Year], "en-US"), " vs ", Text.From([Next Year], "en-US")})

     

    Duplicate a new table:

    Previous Year:

    =[Year]-1

    Combo Year 

    =Text.Combine({Text.From([Previous Year], "en-US"), " vs ", Text.From([Year], "en-US")})

     

    Result after appending queries as new:

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, Kishore_Sr4 

    Plese check my attached pbix file.

    You could try adding custom columns that combines the next/previous year's numbers with the current year's numbers and then combine the query results.

    First Table:

    Next Year

    =[Year]+1

    Combo Year 

    =Text.Combine({Text.From([Year], "en-US"), " vs ", Text.From([Next Year], "en-US")})

     

    Duplicate a new table:

    Previous Year:

    =[Year]-1

    Combo Year 

    =Text.Combine({Text.From([Previous Year], "en-US"), " vs ", Text.From([Year], "en-US")})

     

    Result after appending queries as new:

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Arul's avatar
    Arul
    Super User
    4 years ago

    Kishore_Sr4 ,

    try this by creating new table,

    Year Combo = 
VAR _A = ADDCOLUMNS('Year',"Yearcombo",SWITCH('Year'[Year],
2017,"2017 vs 2018",
2018,"2017 vs 2018",
2019,"2018 vs 2019",
2019,"2018 vs 2019",
2020,"2019 vs 2020",
2020,"2020 vs 2021",
2021,"2020 vs 2021"))

VAR _B = ADDCOLUMNS('Year',"Yearcombo",SWITCH('Year'[Year],
2018,"2018 vs 2019",
2019,"2018 vs 2019",
2019,"2019 vs 2020",
2020,"2020 vs 2021","N/A"))

return UNION(_A,_B)

     

    Output:

    Let me know if it works or not.

     

    Thanks,

    Arul