Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @Calvin69 ,
It's fine.Glad to help.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@Calvin69 Can you paste that as text or provide a link to a file with sample data? That will speed the entire process.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |