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:   Unit Amendment Type Final Rent Start Date End Date A Original Lease £100 01/01/2020 31/12/2023 A Termination     31/12/2023...
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
        )
    )
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.