Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Calvin69
Helper III
Helper III

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.

Calvin69_0-1630253815520.png

Can anyone  kindly help?

 

Thanks

H

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

vkellymsft_0-1630571996352.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

Hi  @Calvin69 ,

 

How to get the expected output you show?

 

Best Regards,
Kelly

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

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

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:

vkellymsft_0-1630571996352.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Hi @v-kelly-msft ,

 

Aplogies for my late response.

You are an absolute legend.

 

Thanks.

Hi  @Calvin69 ,

 

It's fine.Glad to help.

 

Best Regards,
Kelly

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.