Reply
AnthonyJoseph
Resolver III
Resolver III

Find preivous year value for multiple columns

Hello Community,

 

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

 

Table1:

 

IdYearCountryCitySalesProfitDifference
12020IndiaDelhi10050-50
22021IndiaMumbai20090-110
32022IndiaDelhi300150-150
42020NigeriaAbuja5010-40
52021NigeriaLagos3020-10
62022NigeriaAbuja5540-15
72020ZimbabweHarare6020-40
82021ZimbabweHarare3025-5
92022ZimbabweHarare5019-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:

IdYearCountryCitySalesProfitDifferencePrior year salesPrior year profitPrior year difference
12020IndiaDelhi10050-50000
22021IndiaMumbai20090-110000
32022IndiaDelhi300150-15010050-50
42020NigeriaAbuja5010-40000
52021NigeriaLagos3020-10000
62022NigeriaAbuja5540-155010-40
72020ZimbabweHarare6020-40000
82021ZimbabweHarare3025-56020-40
92022ZimbabweHarare5019-313025-5

 

Please can anyone help me here

 

Thanks,

Joseph

 

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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.

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)

 

AnthonyJoseph_0-1651146073787.png

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

AnthonyJoseph_1-1651146175430.png

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

 

Thanks

 

 

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)