Renewed Tenancy Value KPI

[Calvin69]

Hi all, @Greg_Deckler 

I am required to build a KPI that shows the total value of rent originated from any current tenant that had their Contract renewed.

Table containing the data contains many properties, buildings & units: (Expected Results columns is what I am looking for to achieve) I arranged the data by "Start Date" value but it is not arranged in that order in the actual data table.

Can anyone  kindly help?

Thanks

H

[v-kelly-msft]

Hi  @Calvin69 ,

How to get the expected output you show?

Best Regards,
Kelly

Did I answer your question? Mark my areply as a solution!

[Calvin69]

Hi @v-kelly-msft ,

Logic should be as follow:

When a start date for a Location"Title" is changed to Greater value and the Client"Tenant Inclusive" is still the same comparing it with previous (Location "Title") entries show the "Rent" value.

Thanks for looking into this 🙂

H

[v-kelly-msft · Accepted solution]

Hi  @Calvin69 ,

First create an index column;

Then create 2 columns as below:

_index = CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[Title]=EARLIER('Table'[Title])&&'Table'[TenantN]=EARLIER('Table'[TenantN])&&'Table'[StartDate]>EARLIER('Table'[StartDate])))

Column =
VAR _previousindex =
    CALCULATE (
        MAX ( 'Table'[_index] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
VAR _previousrent =
    CALCULATE (
        MAX ( 'Table'[Rent] ),
        FILTER (
            'Table',
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
                && 'Table'[Title] = EARLIER ( 'Table'[Title] )
                && 'Table'[TenantN] = EARLIER ( 'Table'[TenantN] )
        )
    )
RETURN
    IF (
        'Table'[Index] = _previousindex,
        IF ( 'Table'[Rent] > _previousrent, 'Table'[Rent], _previousrent )
    )

And you will see：

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Calvin69]

Hi @v-kelly-msft ,

Aplogies for my late response.

You are an absolute legend.

Thanks.

[v-kelly-msft]

Hi  @Calvin69 ,

It's fine.Glad to help.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Greg_Deckler]

@Calvin69 Can you paste that as text or provide a link to a file with sample data? That will speed the entire process.