The Date table does not relate to another date table

[coliveira77]

Hello friends from Power Bi,

For a week now I have been researching how to include a new table of interest rates and access according to the period of each line.

Link to the PBIX files 

[DataInsights]

@coliveira77,

Do you want a measure or calculated column? Here's the DAX for a measure, with the assumption that if no row exists in fReajustes for a Contract/Date, return the original amount.

Adjusted Amount = 
VAR vAmount =
    SUM ( dbo[item_valor_new] )
VAR vContract =
    MAX ( Contracts[Contracts] )
VAR vDate =
    MAX ( dCalendar[Date] )
VAR vRate =
    LOOKUPVALUE (
        fReajustes[adjustment percetual],
        fReajustes[Contracts], vContract,
        fReajustes[adjustment date], vDate
    )
VAR vResult =
    IF ( ISBLANK ( vRate ), vAmount, vAmount * vRate )
RETURN
    vResult

[coliveira77]

Hello, @DataInsights Thank you for your help, sorry for the delay, I was trying to solve it with your help.

I made a test and still didn't get the expected result, so I made an example of the result I want.

A column calculated according to the "Fat_new" lines of the "dbo" table, multiply with the lines of the "percentage adustment" column
table "fRreaadjustment", respecting the posting dates, not having corresponding value in the rows of the column "percentage adustment" of the table "fReajustes", return the same value of the rows "Fat_new" of the table "dbo".

[DataInsights]

@coliveira77,

Try this measure:

Adjusted Amount = 
VAR vAmount =
    SUM ( dbo[Fat_new] )
VAR vContract =
    MAX ( Contracts[Contracts] )
VAR vDate =
    MAX ( dCalendar[Date] )
VAR vMaxRateDate =
    CALCULATE (
        MAX ( fReajustes[adjustment date] ),
        ALL ( fReajustes ),
        fReajustes[adjustment date] <= vDate,
        fReajustes[Contracts] = vContract
    )
VAR vRate =
    LOOKUPVALUE (
        fReajustes[adjustment percetual],
        fReajustes[Contracts], vContract,
        fReajustes[adjustment date], vMaxRateDate
    )
VAR vResult =
    IF ( ISBLANK ( vRate ), vAmount, vAmount * vRate )
RETURN
    vResult

[coliveira77]

Thanks for the help @DataInsights, but when I apply it to my file, it comes up with another value, I did some variations without success.

[DataInsights]

@coliveira77,

Try breaking it down so you can see each component of the calculation. After RETURN in the measure, put vRate instead of vResult. This will display the rate being used. Do the same with each variable to identify the problem.

[coliveira77 · Accepted solution]

Hello Friend @DataInsights thanks for the help, I was able with this formala see the value of the adjustment, then I make another measurement for the final value.

1 Valor Reajuste =

CALCULATE (

LASTNONBLANKVALUE (fReajustes[adjustment date] , [sum_reajustes]),

FILTER (

ALL ( dCalendar[Date]),

dCalendar[Date] <= MAX ( dbo[measure_date]

)

))