Divide values based on date

[pamorim]

Hello everyone,

I'm fairly new to PowerBI and I can't make this measure work. I have two tables:

1 - The first one repeats the names of every city, adding the year in another column.

Amount City Code Month Year RISP

2 - The other one doesn't repeat the city, but adds a lot of columns for years (each "year" column is the city population in that year).

Code City RISP 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019

I need to write a measure that divides the amount by the population, but using the correct population value for that year. Any ideas how I'd do that?

Thanks!

[Cmcmahan · Accepted solution]

The easiest way would be to unpivot your data. Check here for a guide.

https://radacad.com/pivot-and-unpivot-with-power-bi

Once your data is in multiple rows per country, you should be able to used SELECTEDVALUE to find the correct value.