Sum year and previous year from that year selected from filtered list

[avinashbeepath]

Hello all,

I hope my title was not too misleading. I've researched my problem but still cant seem to find a robust solution to my problem. Can anyone please assist? 

My year table above as an example contains multiple years. But I would like to be able to select 2015 and get that year's passenger populated in the card vs the preious year which in this case would be 2014. How do I accomplish this? 

Thanks for any ideas contributed.

[Sean · Accepted solution]

@avinashbeepath

Max Year = CALCULATE( [Your Measure], FILTER (Table, Table[Year Column]=MAX(Table[Year Column]) ) )

Max Year -1 = CALCULATE ( [Your Measure], FILTER (Table, Table[Year Column]=MAX(Table[Year Column]) -1 ) )

YoY Change = [Max Year] - [Max Year -1]

YoY % Change = DIVIDE ( [YoY Change], [Max Year-1], 0 )

Let me know if this works!

[avinashbeepath]

Hi Sean,

Thanks sooo much for your response!! It's a excellent start to a solution however I'm getting the below error response when i try to display the value.

 

Also this is the formula i used - 

MAX YEAR = CALCULATE('2015 vs 2016'[REVENUE MEASURE],FILTER('d_DATE_TBL','d_DATE_TBL'[Year]=max(d_DATE_TBL[Year])))

Can you identify where I went wrong? 

Edit - The name of the table is misleading 2015 vs 2016 as there is 2014 data in there as well. I was having major difficulty getting this part so I was only using 2016 vs 2015 data.

Thanks again so much for your willingness to assist.

[Sean]

What is the formula in the [Revenue Measure] ?

And what data type is the  [Year] Column?

[avinashbeepath]

I have the Year field in a datekey table and I just checked it out and it's set as a text field. Also the REVENUE MEASURE = sum('2015 vs 2016'[REVENUE]).

Thanks Sean.

Regards,

Avinash

[Sean]

Why is it text? Does it just contain the number? Like 2016?

[avinashbeepath]

Ahhhh thank you So much Sean. I changed it to number and the formula worked immediately! It was imported as a text from excel. 

You are the man...thanks again for your time and assistance! 

Best Regards.