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

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!

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

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

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

    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'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!