Create a new column in the same table by returning the value of previous year

[artur4]

Hello,

I need to create a new column in my main table which takes the values from another column of the same table but -1 year.

To better explain, I have the following table: 

I need to create another FX column which is taking for each country and for each month the FX of the same month but previous year. I need to have it in the same table because then I need to create a measures SUMX (FX (year -1) * Sales).

I even tried to create another table (FX_Table) with just the FX (year -1), but when I try to do the SUMX, it does not work because I have the data in different tables. Also I tried to use RELATD but it doesn't work as I have a Date Table (Calendar) and a Country Mapping Table in the middle linked to the main table and FX_table.

 FX_table = SUMMARIZECOLUMNS('main table'[Country], 'main table'[Month], 'main table'[Date], "FX C", CALCULATE(MAX('main table'[fx_reported]), SAMEPERIODLASTYEAR('main table'[Date])))

[Wilson_ · Accepted solution]

Hey artur4,

Try this as a calculated column:

fx_reported_last_year =
VAR SelCountry = 'main table'[Country]
VAR LastYr = EDATE ( 'main table'[Date], -12 )
VAR Result =
MINX (
    FILTER (
        'main table',
        'main table'[Country] = SelCountry &&
        'main table'[Date] = LastYr
    ),
    'main table'[fx_reported]
)

RETURN Result

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.