COLLECT LAST DATE VALUE FOR EACH YEAR

[nasolme]

Hi community !

I work in real estate structured financed and i get the update of the real estate assets.

As you can see on the screenshot i attach, values are not updated every year. So i take the last known value as absolute value (see second screenshot).

I two tables : CALENDAR and VALUES HISTORY

How can i get the result of the second screen (with max value column) directly in POWER BI ? 





Thanks a lot for your time, take care of you,

Nasolme

[PaulDBrown · Accepted solution]

Try:

Last value by Year =
VAR LY =
    CALCULATE (
        LASTNONBLANK ( 'Calendar Table'[Date], [Sum Valorisation] ),
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
        )
    )
RETURN
    CALCULATE (
        [Sum Valorisation],
        FILTER ( ALL ( 'Calendar Table' ), 'Calendar Table'[Date] = LY )
    )

[PaulDBrown · Accepted solution]

Try:

Last value by Year =
VAR LY =
    CALCULATE (
        LASTNONBLANK ( 'Calendar Table'[Date], [Sum Valorisation] ),
        FILTER (
            ALL ( 'Calendar Table' ),
            'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
        )
    )
RETURN
    CALCULATE (
        [Sum Valorisation],
        FILTER ( ALL ( 'Calendar Table' ), 'Calendar Table'[Date] = LY )
    )

[nasolme]

That's exactly what i need. 

muchas Gracias

[nasolme]

@PaulDBrown 

Just a last question, Is it possible to obtain the sum of the retained values ​​per year?

When I select more than one asset, last value per line work's fine but the grand total is false. I need to calculate portfolio ratios.

Thanks you

[PaulDBrown · Accepted solution]

With this model:

 and this measure:

With Totals =
SUMX ( 'Deal Table', [Last value by Year] )

[johnt75]

You can try creating a measure as

Last known value =
var maxDate = MAX('Calendar'[Date])
return SELECTCOLUMNS( 
   CALCULATETABLE( TOPN(1, 'Values History', 'Values History'[Date_valorisation]), 
      REMOVEFILTERS('Calendar'[Date]), 'Values History'[Date_valorisation] <= maxDate ),
"@val", 'Values History'[Valorisation_total])

[nasolme]

Hi @johnt75 and thanks you for your time,

But it doesn't works. I would like when i filter with calendrier[date], if i select 31/12/2021 power bi give me the last known value (12,5 M€), if i select 31/12/2018, he get 10, 9 M€ etc...

[johnt75]

That's what I would expect this to return, and it seems to be working in my tests. What results are you getting ?

[amitchandak]

@nasolme ,

calculate( Lastnonblankvalue(Table[Date Valorization], max(Table[Valorization Value]) ))

calculate( Lastnonblankvalue(Date[Date], max(Table[Valorization Value]) ))

[nasolme]

@amitchandak Thanks for your answer, but this isn't the expected result (see on screenshot) :

I need for each year a valuation who appear exactly like on the example given earlier.
With your solution, i get the same result without measure.