Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
artur4
Helper I
Helper I

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

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: 

artur4_1-1713786275608.png

 

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])))

 

artur4_0-1713787675955.png

 

 

 

1 ACCEPTED SOLUTION
Wilson_
Memorable Member
Memorable Member

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.

View solution in original post

1 REPLY 1
Wilson_
Memorable Member
Memorable Member

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.