Create a new column to get the % change in current & previous year

[harshadrokade]

Hi friends,

I have table as below-

Original table

Student name	periodendson	A	B	C	D
XYZ1	31-12-2019	57	80	44	37
XYZ1	31-12-2020	33	51	39	21
XYZ1	31-12-2021	26	78	48	37
XYZ2	31-12-2019	53	27	57	61
XYZ2	31-12-2020	60	70	54	69
XYZ2	31-12-2021	29	43	66	74
XYZ3	31-12-2019	39	55	73	34
XYZ3	31-12-2020	31	65	42	66
XYZ3	31-12-2021	73	31	48	79
XYZ4	31-12-2019	69	50	57	30
XYZ4	31-12-2020	72	77	62	68
XYZ4	31-12-2021	43	50	70	31

I want to create a table visual like below with Change column newly created(with dax). Can u pls help me to know how can I create the same?

Expected visual-

Parameter	2019	2020	2021	Change in 2021 compared to 2020
A	57	33	26	-21%
B	80	51	78	53%
C	44	39	48	23%
D	37	21	37	76%

One way is that I unpivot the original table & then it reflects in below format which allows me to create a matrix table in required format. Now I am unable to understand how to add a change column in the table visual as explained above in the visual. Appreciate your help sir.

Univot table-

Student name	periodendson	Parameter	Values
XYZ1	31-12-2019	A	57
XYZ1	31-12-2019	B	80
XYZ1	31-12-2019	C	44
XYZ1	31-12-2019	D	37
XYZ1	31-12-2020	A	33
XYZ1	31-12-2020	B	51
XYZ1	31-12-2020	C	39
XYZ1	31-12-2020	D	21
XYZ1	31-12-2021	A	26
XYZ1	31-12-2021	B	78
XYZ1	31-12-2021	C	48
XYZ1	31-12-2021	D	37
XYZ2	31-12-2019	A	53
XYZ2	31-12-2019	B	27
XYZ2	31-12-2019	C	57
XYZ2	31-12-2019	D	61
XYZ2	31-12-2020	A	60
XYZ2	31-12-2020	B	70
XYZ2	31-12-2020	C	54
XYZ2	31-12-2020	D	69
XYZ2	31-12-2021	A	29
XYZ2	31-12-2021	B	43
XYZ2	31-12-2021	C	66
XYZ2	31-12-2021	D	74

[v-janeyg-msft · Accepted solution]

Hi, @harshadrokade 

I created a sample to meet your needs.

• First of all, the Univot table is required.
• Create a column to flag the last three year data in your matrix table and use it in filter pane.
  (Since I don't know why it's not 2022, I let maxyear -1, You can modify it according to what you want)

Column =
VAR maxyear =
    YEAR ( TODAY () ) - 1
VAR minyear = maxyear - 2
RETURN
    IF (
        YEAR ( [periodendson] ) >= minyear
            && YEAR ( [periodendson] ) <= maxyear,
        1,
        0
    )

• Create a measure and use it in meatrix vlaue to show results.

I calculate the sum of the current year and the comparison data separately according to your needs, and force the sum format to remain unchanged, otherwise it will be unified into a percentage format.

Measure =
VAR maxyear =
    YEAR ( TODAY () ) - 1
VAR minyear = maxyear - 1
RETURN
    IF (
        YEAR ( SELECTEDVALUE ( 'Table'[periodendson] ) ) = BLANK (),
        DIVIDE (
            CALCULATE ( SUM ( 'Table'[Value] ), YEAR ( 'Table'[periodendson] ) = maxyear )
                - CALCULATE ( SUM ( 'Table'[Value] ), YEAR ( 'Table'[periodendson] ) = minyear ),
            CALCULATE ( SUM ( 'Table'[Value] ), YEAR ( 'Table'[periodendson] ) = minyear )
        ),
        FORMAT ( SUM ( 'Table'[Value] ), "General Number" )
    )

• Change some formats.

Results:

Below is my sample.

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[ALLUREAN]

Hi, @harshadrokade 

You can try this example:

https://www.dropbox.com/s/d070ytj1309cokn/%25%20change%20in%20current%20%26%20previous%20year%20%28a...

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[harshadrokade]

Thanks @ALLUREAN  Sir.

In Option 2, I can see that te measures you have created is a static measure which provides the value for only 2019, 2020 & 2021 but if the new data gets added for upcoming years to the original data, my table visual will not get updated.

Also in Option 1, it works when I have a slicer on the screen. I don't have a slicer on dashbaord & so I want to always show current + last 2 years values from the database.

How can I create a visual that will always show me the last three years in table & will show the change in latest year as compared to previous year. Sorry for not putting this requirement earlier.