Populate Data to end of Date Table from last row entry

[CBPart]

Hi PBI Community,

I'm working of building a PPMT dashboard, calculating on a variable rate not fixed for life of loan.....I've a table of interest rate changes on date change and linked to a date table to calculate interest changes daily.

Dates and rate have all matched up until 9/11/2023 and I need the rate to continue aganist dates until next interest rate change.

So far I’ve used the following measure:

Rate = maxx(FILTER('CurrentRate','Date Table'[Date]>='CurrentRate'[Date]&&'Date Table'[Date]<='CurrentRate'[End Date]),'CurrentRate'[Rate])

How I can include last interest rate to the end of my date table range?

Thanking all the gurus out there

[Anonymous · Accepted solution]

Hi @CBPart 

You can refer to the following measure

Measure =
VAR a =
    MAXX ( ALLSELECTED ( CurrentRate ), [Date] )
RETURN
    IF (
        MAX ( 'Date Table'[Date] ) >= a,
        MAXX ( FILTER ( ALL ( CurrentRate ), [Date] = a ), [Rate] ),
        CALCULATE (
            MAX ( CurrentRate[Rate] ),
            CurrentRate[Date] <= MAX ( 'Date Table'[Date] ),
            CurrentRate[End Date] > MAX ( 'Date Table'[Date] )
        )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.