Calculate previous year value based on slicer selection

[AnthonyJoseph]

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

[tamerj1 · Accepted solution]

Then you refer to it as 'Table' not Table becuase it is a resereved word in DAX

[tamerj1]

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]

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]

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

[AnthonyJoseph]

Hi @tamerj1 

Yes, the name of the table that I m using is "Table" only but still I'm facing the same error. If possible can you share the pbix?

Thanks,

Joseph

[tamerj1 · Accepted solution]

Then you refer to it as 'Table' not Table becuase it is a resereved word in DAX

[AnthonyJoseph]

Hi @tamerj1 

Sorry, if my example was misguiding. I think in the solution you have provided we are returning the maximum values of the previous year but Ideally we need to sum the prior year sales value for example:

Input: 

Year	Country	City	Sales	Profit
2020	India	Delhi	100	50
2021	India	Mumbai	200	90
2021	India	Delhi	3300	300
2020	India	Delhi	2000	50

Expected output:

Year	Country	City	Sales	Profit	Difference	Prior year Sales	Prior year Profit	Prior year Difference
2020	India	Delhi	100	50	-50	0	0	0
2021	India	Mumbai	200	90	-110	0	0	0
2021	India	Delhi	3300	300	-3000	2100	100	-2000
2020	India	Delhi	2000	50	-1950	0	0	0

[tamerj1]

@AnthonyJoseph 

If I correctly understand then you just need to replace MAXX with SUMX 

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 =
    SUMX ( FILTER ( CountryCityTable, 'Table'[Year] = PriorYear ), 'Table'[Sales] )
RETURN
    PeriorYearSales

[AnthonyJoseph · Accepted solution]

Ah! Thanks @tamerj1 Works perfectly now 🙂