Generated Income based on a Variable

[Calvin69]

Hi everyone,😊

Thank you in advance for attempting to read through this and for any help that you could provide me with.

I am trying to build a measure or a column that would allow me to do the following:

Table:

Date	Tenant	Start	Review	End	Rent
01/01/2019	Vacant	01/10/2019		01/10/2021	0
01/01/2020	Voda	01/01/2020		01/10/2020	10000
01/01/2020	Hitash	15/11/2020	01/06/2021	01/10/2021	15000
01/01/2020	Sports ltd	01/01/2020	15/06/2021	31/12/2021	5000
01/07/2021	Vacant	01/10/2019		01/10/2021	0
01/07/2021	Voda	15/11/2020		01/10/2021	10000
01/07/2021	Hitash	15/11/2020		01/10/2021	17000
01/07/2021	Sports ltd	01/01/2020	01/11/2021	31/12/2021	7000

Target: Total of "4000" should appear based on the following logic

	On the 01/07/2021
	If new review date is Blank or Greater than previous review date and the Tenant Name is the same then the following should take place
Hitash	New Rent Value (17000) - Old Rent Value (15000) = 2000
Sports ltd	New Rent Value (7000) - Old Rent Value (5000) = 2000
	Total Rent difference = 2000 + 2000 = 4000

Thanks

H

[Greg_Deckler · Accepted solution]

@Calvin69 Made a few assumptions along the way but maybe this:

Measure = 
    VAR __Table = 'Table4'
    VAR __MaxDate = MAX('Table4'[Date])
    VAR __PreviousDate = MAXX(FILTER('Table4',[Date]<__MaxDate),[Date])
    VAR __MaxDateTable = FILTER(__Table,[Date]=__MaxDate)
    VAR __PreviousDateTable = FILTER(__Table,[Date]=__PreviousDate)
    VAR __FinalTable = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                __MaxDateTable,
                "__Include",
                        VAR __Review = [Review]
                    RETURN
                        IF(ISBLANK([Review]) || COUNTROWS(FILTER(__PreviousDateTable,[Review]<__Review))>0,1,0)
            ),
            "__Diff",
                    VAR __Tenant = [Tenant]
                RETURN
                    IF([__Include]=1,[Rent] - MAXX(FILTER(__PreviousDateTable,[Tenant]=__Tenant),[Rent]),0)
        )
RETURN
    SUMX(FILTER(__FinalTable,[__Include]=1),[__Diff])

[Calvin69]

Hi @Greg_Deckler ,

Spot on mate, wonderful and thank you so much.

Just wondering why do you add __ before each variable name?

H

[Greg_Deckler]

@Calvin69 Best explained as old habit I suppose. I found it advantageous to flag things I created versus what is actually in the data model. Comes in particularly handly when using ADDCOLUMNS and more complex measures as it can become a bit confusing to figure out what is being referred to in what context. I use a double underscore because it fits the rhythm of my typing better.