Forum Discussion

Calvin69's avatar
Calvin69
Icon for Helper III rankHelper III
5 years ago
Solved

Renewed Tenancy Value KPI

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 pro...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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's avatar
Calvin69
Icon for Helper III rankHelper III
5 years ago

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's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
    Calvin69
    Icon for Helper III rankHelper III
    4 years ago

    Hi v-kelly-msft ,

     

    Aplogies for my late response.

    You are an absolute legend.

     

    Thanks.

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi  Calvin69 ,

       

      It's fine.Glad to help.

       

      Best Regards,
      Kelly

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