Help with a Calculated Column

[Richard_Clipsto]

Hi,

I have a table which looks a bit like this:

Unit	Amendment Type	Final Rent	Start Date	End Date
A	Original Lease	£100	01/01/2020	31/12/2023
A	Termination			31/12/2023
A	Renewal	£150	01/04/2024	31/03/2028
B	Original Lease	£200	01/01/2020	31/12/2029

I'm trying to get a calculated column that for each Unit it provides me with the previous final rent. I only want it to look at rows which have Original Lease or Renewal as these will be the relevant records. I've tried using topn but I can't get the filters to work to allow me to look at only the relevant unit, and then only the relevant Amendment Types.

Thank you!

[Anonymous]

Hi @Richard_Clipsto ,

You can try formula like below to create calculated column:

Previous1 = 
LOOKUPVALUE(
    'Table'[Final Rent],
    'Table'[Unit], 'Table'[Unit],
    'Table'[Amendment Type], "Original Lease",
    'Table'[Start Date], 
    CALCULATE(
        MAX('Table'[Start Date]),
        FILTER(
            'Table',
            'Table'[Unit] = EARLIER('Table'[Unit]) &&
            'Table'[Amendment Type] IN {"Original Lease", "Renewal"} &&
            'Table'[Start Date] < EARLIER('Table'[Start Date])
        )
    )
)

or below formula, but you need to manipulate the columns in the power query to change their data type to number:

Previous2 = 
SUMX(
    TOPN(
        1,
        FILTER(
            'Table',
            'Table'[Unit] = EARLIER('Table'[Unit]) &&
            'Table'[Amendment Type] IN {"Original Lease", "Renewal"} &&
            'Table'[Start Date] < EARLIER('Table'[Start Date])
        ),
        'Table'[Start Date], DESC
    ),
    'Table'[Text After Delimiter]
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bibiano_Geraldo · Accepted solution]

Hi @Richard_Clipsto , Please try the following Dax code to create the calculated column:

PreviousFinalRent = 
VAR CurrentUnit = Table[Unit]
VAR CurrentStartDate = Table[Start Date]
RETURN 
    CALCULATE(
        MAX(Table[Final Rent]),
        FILTER(
            Table,
            Table[Unit] = CurrentUnit &&
            Table[Amendment Type] IN {"Original Lease", "Renewal"} &&
            Table[Start Date] < CurrentStartDate
        )
    )

[Richard_Clipsto]

This is great thank you