Calculate Actual by previous year in the range of 2 different column date

[fabiolamelo]

Hi everyone,

I`m  stuck trying to find a solution to the following problem:

I have 2 tables (Tenement Table and transactions table) the relation between both is the tenement id.

I need to calculate the actual (Transaction table) by Tenement Aniversary date (Tenement Table) just during the Previous year 

Example:
Tenement Table

Tenement ID |  Tenement Anniversary Date

TEN1 | 01/01/2020

TEN2 | 01/01/2021

Note: This table just have one date for each tenement 

Transaction Table 
Tenement ID |  Tenement Transaction Date | Actual

TEN1 | 01/09/2019 | $100

TEN1 | 02/09/2019 | $200

TEN1 | 01/11/2018 | $300

TEN2 | 01/01/2020 | $10
TEN2 | 02/02/2020 | $20

TEN2 | 02/03/2020 | $30  

Result

Tenement ID |  Value
TEN1            | $300

TEN2            | $60

Note: In TEN1 (Transaction table ) we didn't consider the line for 2018 because The tenement Anniversary Date is 01/01/2020 and we just want to know how much was spend in the previous year (in this case 2019)

I`ve combined the 2 tables (Tenement Table and Transaction Table ) and tried to calculate the actual using PREVIOUSYEAR function and it didn't work. Also, I`ve tried to use the Min, Max and the previous function didn`t work as well.

Can you please help me figure out how can I have a solution to this problem?

Unfortunately, I can`t share the data because is a confidential database.

Thank you so much.

Fabi Melo

[Anonymous · Accepted solution]

Result = 
VAR AnniversaryDate = SELECTEDVALUE(Tenement[  Tenement Anniversary Date])
VAR StartDate = EDATE(AnniversaryDate,-12)
VAR TID = SELECTEDVALUE(Tenement[Tenement ID ])
VAR Result =
    SUMX(
        FILTER(
            ALLSELECTED(Transactions),
            Transactions[Tenement Transaction Date ]>=StartDate && 
            Transactions[Tenement Transaction Date ] <= AnniversaryDate &&
            Transactions[Tenement ID ] = TID
        ),
        Transactions[ Actual]
    )
RETURN
Result

[fabiolamelo]

@Anonymous  thank you much, it works 🙂