Forum Discussion

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

Calculate previous year value based on slicer selection

Hello Community,

 

Need your help in implementing a functionality. I have a table as given below:

 


The task here is to display the sum (based on country and city) of previous year of Sales, Profit and difference as different columns along with the current year values as given below.

Expected result:

 

Please can anyone help me here.


Thanks

DAX Calculation
Need Help

8 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi AnthonyJoseph 

    you may try creating measures similar to 

    Prior Year Sales =
VAR CurrentYear =
    MAX ( Table[Year] )
VAR CountryCityTable =
    CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Country], Table[City] ) )
VAR PriorYear =
    MAXX ( FILTER ( CountryCityTable, Table[Year] < CurrentYear ), Table[Year] )
VAR PeriorYearSales =
    MAXX ( FILTER ( CountryCityTable, Table[Year] = PriorYear ), Table[Sales] )
RETURN
    PeriorYearSales
    • AnthonyJoseph's avatar
      AnthonyJoseph
      Icon for Resolver III rankResolver III
      4 years ago

      Thanks tamerj1 

      I tried to use the dax but looks like something is incorrect in the measure. Please refer the screenshot below:

      Please can you help me.
      Thanks again

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Please use the actual name of you table instead of "Table"