Forum Discussion

Richard_Clipsto's avatar
Richard_Clipsto
Frequent Visitor
1 year ago
Solved

Help with a Calculated Column

Hi,


I have a table which looks a bit like this:

 

UnitAmendment TypeFinal RentStart DateEnd Date
AOriginal Lease£10001/01/202031/12/2023
ATermination  31/12/2023
ARenewal£15001/04/202431/03/2028
BOriginal Lease£20001/01/202031/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!

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

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

3 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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
        )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.