Flag and calculate a rent value once tenant's name is changed

[Calvin69]

Hello everyone,

I really appreciate your help with this in advance, I have been trying to make it work for ages and failed miserably so far.

I have a consolidated data table that receives new entries every time a user modifies a SharePoint List. Within this table, multiple Properties, Buildings, Units and tenants are registered.

Keys:

• A Property (PropertyN) consists of Building/s, a Building (BuidlingN) consists of rooms or floors (Title - Location) and each location is assigned to a Tenant (TenantN)

Several KPIs are required, one of which is to have a measure or a new column that would allow me to register (Rent value) whenever a Tenant Value is changed from "Vacant" to "Tenant Name" or from a "Tenant Name" to a "New Tenant Name".

I have added indexes to group all different category with a single code but not so sure how to put them into a good use.

Please view my attached sample data file.

https://docs.google.com/spreadsheets/d/1FLooqHK_mv3XJAsVBo5SVh2Ap4027c3E/edit?usp=sharing&ouid=10088... 

Thanks

H

[Greg_Deckler]

@Calvin69 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[Calvin69]

Hi @Greg_Deckler ,

I really apprecaite your feedback on this.

I have followed your instructions above but it seems that I am still doing somthing wrong:

Dax Code

PBICOM = 
    VAR Curren = '04ConsolidatedEntries'[Rent]
    VAR PreviousDate = MAXX(
                        FILTER('04ConsolidatedEntries','04ConsolidatedEntries'[StartDate.Element:Text] <
                            EARLIER('04ConsolidatedEntries'[StartDate.Element:Text])), '04ConsolidatedEntries'[StartDate.Element:Text])

    VAR Previous = MAXX(FILTER('04ConsolidatedEntries',[StartDate.Element:Text] = PreviousDate), [Rent])
RETURN
  Curren - Previous

Table:

Expected values/behaviour are hilighted within the green column.

Thanks

H

[Greg_Deckler]

@Calvin69 Can you post sample data?

[Calvin69]

Hi @Greg_Deckler 

Sure : https://drive.google.com/drive/folders/1-nhx9yKcLrrH3fcL-V3O7Kb4eSkdRKrH?usp=sharing 

PropertyN Inclusive	BuildGroupIndex1	BuildingN Inclusive	TitleGroupIndex1	Title	TenantGroupIndex	TenantN Inclusive	StartDate.Element:Text	EndDate.Element:Text	VACorNot!	Index0	Index1	Rent	PBICOM	Expected
Test Test Data Property	27	TES Test Data Building	155	Our Test Destination	11	Vacant	01/01/2018 00:00	31/12/2019	1	729	730		-75500
Test Test Data Property	27	TES Test Data Building	155	Our Test Destination	65	Power BI Limited	01/01/2018 00:00	31/12/2019	0	730	731	2000	-73500
Test Test Data Property	27	TES Test Data Building	155	Our Test Destination	65	Power BI Limited	01/01/2018 00:00	31/12/2019	0	731	732	4000	-71500
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	65	Power BI Limited	01/01/2018 00:00	31/12/2019	0	732	733	4000	-71500
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	65	Power BI Limited	01/01/2018 00:00	31/12/2019	0	733	734	4000	-71500
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	65	Power BI Limited	01/01/2020 00:00	31/12/2021	0	734	735	8000	-56568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	65	Power BI Limited	01/01/2020 00:00	31/12/2021	0	735	736	10000	-54568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	65	Power BI Limited	01/01/2020 00:00	31/12/2021	0	736	737	10000	-54568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	65	Power BI Limited	01/01/2020 00:00	31/12/2021	0	737	738	10000	-54568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	65	Power BI Limited	01/01/2020 00:00	31/12/2021	0	752	753	10000	-54568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	65	Power BI Limited	01/01/2020 00:00	31/12/2021	0	753	754	10000	-54568	10000
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	31/12/2021	1	769	770	0	-64568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	31/12/2021	1	770	771	0	-64568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	31/12/2021	1	771	772	25000	-39568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	31/12/2021	1	772	773	35000	-29568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	31/12/2021	1	773	774	5000	-59568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	31/12/2021	1	774	775	5000	-59568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	31/12/2021	1	775	776	5000	-59568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	31/01/2022	1	776	777	5000	-59568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	06/02/2022	1	777	778	5000	-59568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	11	Vacant	01/01/2020 00:00	06/02/2022	1	778	779	8000	-56568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	66	MCdo	01/01/2020 00:00	06/02/2022	0	779	780	12000	-52568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	66	MCdo	01/01/2020 00:00	06/02/2022	0	1896	1897	12000	-52568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	66	MCdo	01/01/2020 00:00	06/02/2022	0	1897	1898	12000	-52568
Test Test Data Property	27	TES Test Data Building	156	Our Test Destination / V2	66	MCdo	01/01/2020 00:00	06/02/2022	0	1898	1899	20000	-44568	20000
Test Test Data Property	27	TES Test Data Building	180	Mansion Central - Test	77	Fresh Water LTD	01/06/2020 00:00	01/06/2021	0	1899	1900	50000	41700	50000
Test Test Data Property	27	TES Test Data Building	180	Mansion Central - Test	78	Bozo Fishing LTD	01/06/2020 00:00	01/06/2021	0	1900	1901	100000	91700
Test Test Data Property	27	TES Test Data Building	180	Mansion Central - Test	78	Bozo Fishing LTD	01/06/2020 00:00	01/06/2021	0	1901	1902	100000	91700
Test Test Data Property	27	TES Test Data Building	180	Mansion Central - Test	78	Bozo Fishing LTD	01/06/2020 00:00	01/06/2021	0	1902	1903	120000	111700
Test Test Data Property	27	TES Test Data Building	180	Mansion Central - Test	78	Bozo Fishing LTD	01/06/2020 00:00	01/06/2022	0	1903	1904	120000	111700
Test Test Data Property	27	TES Test Data Building	180	Mansion Central - Test	78	Bozo Fishing LTD	01/06/2020 00:00	01/06/2022	0	1904	1905	120000	111700	120000
													Total	200000

Thanks