Forum Discussion

AnthonyJoseph's avatar
AnthonyJoseph
Icon for Resolver III rankResolver III
4 years ago

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

 

DAX
Prior year filtering

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      AnthonyJoseph
      Icon for Resolver III rankResolver III
      4 years ago

      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