Find preivous year value for multiple columns

[AnthonyJoseph]

Hello Community,

New to powerbi and I have data as in table 1.

Table1:

Id	Year	Country	City	Sales	Profit	Difference
1	2020	India	Delhi	100	50	-50
2	2021	India	Mumbai	200	90	-110
3	2022	India	Delhi	300	150	-150
4	2020	Nigeria	Abuja	50	10	-40
5	2021	Nigeria	Lagos	30	20	-10
6	2022	Nigeria	Abuja	55	40	-15
7	2020	Zimbabwe	Harare	60	20	-40
8	2021	Zimbabwe	Harare	30	25	-5
9	2022	Zimbabwe	Harare	50	19	-31

Now, I have to create a chart/table visual where I have to show prior year data w.r.t City-Country (as measure) for each current year selection (as in table below).
Expected output:

Id	Year	Country	City	Sales	Profit	Difference	Prior year sales	Prior year profit	Prior year difference
1	2020	India	Delhi	100	50	-50	0	0	0
2	2021	India	Mumbai	200	90	-110	0	0	0
3	2022	India	Delhi	300	150	-150	100	50	-50
4	2020	Nigeria	Abuja	50	10	-40	0	0	0
5	2021	Nigeria	Lagos	30	20	-10	0	0	0
6	2022	Nigeria	Abuja	55	40	-15	50	10	-40
7	2020	Zimbabwe	Harare	60	20	-40	0	0	0
8	2021	Zimbabwe	Harare	30	25	-5	60	20	-40
9	2022	Zimbabwe	Harare	50	19	-31	30	25	-5

Please can anyone help me here

Thanks,

Joseph

[v-chenwuz-msft]

Hi @AnthonyJoseph ,

These three measures are below:

Prior year sales =
VAR _prior =
    TOPN (
        1,
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Country] = SELECTEDVALUE ( 'Table'[Country] )
                && [City] = SELECTEDVALUE ( 'Table'[City] )
                && [Year] < SELECTEDVALUE ( 'Table'[Year] )
        ),
        [Year], DESC
    )
RETURN
    SUMX ( _prior, [Sales] )

Prior year profit =
VAR _prior =
    TOPN (
        1,
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Country] = SELECTEDVALUE ( 'Table'[Country] )
                && [City] = SELECTEDVALUE ( 'Table'[City] )
                && [Year] < SELECTEDVALUE ( 'Table'[Year] )
        ),
        [Year], DESC
    )
RETURN
    SUMX ( _prior, [profit] )

Prior year difference = [Prior year profit]-[Prior year sales]

Pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[AnthonyJoseph]

Thanks @v-chenwuz-msft I can see the data populated correctly without any slicer selection (from year) but when I choose a year from the slicer, all previous year values are turing to be 0.

Without any slicer selection I can see previous year data (example 2022 - India)

If selection is made then the preious year value is blank/zero.

Please can you help me to show previous value even when the slicer selection is made.

Thanks