How to compare the values based on the user's selected year Vs last year.

[Chari]

Hello,
I have a year dropdown and need to compare the values based on the user's selected year Vs last year.

Ex1: If we choose 2023, the comparison should be 2023 vs. 2022, and the legend should be 2023 and 2022.
Ex2: if we select 2020, the comparison should be 2020 against 2019, and the legend should be 2020 and 2019.

Last year should be taken automatically, without the user having to select it.
The legend should also be updated dynamically.

[Anonymous · Accepted solution]

Hi @Chari ,

I made simple samples and you can check the results below:

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[suparnababu8]

Hi @Chari 

1. Create a Date Table: Ensure you have a date table with a column for the year.

2. Create Measures for Sales:

   • Total Sales 

Total Sales = SUM(Source[Sales])

• Sales for Selected Year: 

Sales Selected Year = 
CALCULATE(
    [Total Sales],
    YEAR(Source[Date]) = SELECTEDVALUE('DateTable'[Year])
)

• Sales for Previous Year:

Sales Previous Year = 
CALCULATE(
    [Total Sales],
    YEAR(Source[Date]) = SELECTEDVALUE('DateTable'[Year]) - 1
)
​

• Create a Measure for the Difference 

Sales Difference % = 
VAR SelectedSales = [Sales Selected Year]
VAR PreviousSales = [Sales Previous Year]
RETURN
IF(
    NOT ISBLANK(SelectedSales) && NOT ISBLANK(PreviousSales),
    DIVIDE(SelectedSales - PreviousSales, PreviousSales, 0),
    BLANK()
)

• Create a Dynamic Legend:

  • Legend for Selected Year 

Legend Selected Year = "Sales " & SELECTEDVALUE('DateTable'[Year])

• Legend for Previous Year:

Legend Previous Year = "Sales " & (SELECTEDVALUE('DateTable'[Year]) - 1)
​

This setup will ensure that your comparison and legend are both dynamic and accurate.
If you need more information pls go through https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

[Thennarasu_R]

Hi @suparnababu8 

Can you help on this one? How we can insert measures into the legend , Still Confusing 

Thanks,
Thennarasu R

[Thennarasu_R]

Hi @Chari 

We can't change the Legend header dynamically. In legend, we can add only columns but can't add measures there is no option fx also.if you required have one  way you should customized turn off legend into bar chart put 2 card visuals in card visuals we can insert measure it will chnage based on slicer. We can this option only.

Thanks,
Thennarasu R

If help to your questions , Mark accepted a solution and Give Kudos !

[Chari]

Hello @Thennarasu_R  thank you for your speedy response.

We can manage the legend based on user selection, but my specific request is that when selecting a year from the drop down list, it should automatically compare to the previous year.

[Thennarasu_R]

Hi @Chari 

I understand your requirements based on the selection we can show the comparison in a bar chart.Should created two different measure one is selected year  and another is Previous year measure it will be dynamically. But visuald legend we can't set dynamically in power bi.

Thanks,
Thennarasu R

If help to your questions , Mark accepted a solution and Give Kudos !