How to get the value of the last date for each ID

[kostaszogo21]

Hello guys, 

I am relative new to Power BI and i would really appreciate all the help i can get for what seems as an easy task for all you experts.
I have a table that looks like this:

 What i want to do is find the last (according to date) non-blank value and put it in a new column. That value has to be shown in all the rows of the connection_id. So my desired result will have to look like that:

Unfortunately no pbix file is available. I am just looking for the DAX that will allow me to create that new column.
I know you got it guys and you will sure make me look good to my boss!
Thanks in advance

[moizsherwani · Accepted solution]

@kostaszogo21  here is my solution, I am sure there are more efficient ways which I will be happy to learn from my fellow experts / super users. I have also attached the sample pbix file for your reference.

LastNonBlankValue = 
VAR MaxNonBlankDateForConnection =
    CALCULATE (
        MAX ( SampleData[date] ),
        FILTER (
            SampleData,
            SampleData[value] <> ""
                && NOT ( ISBLANK ( SampleData[value] ) )
                    && SampleData[connection_id] = EARLIER ( SampleData[connection_id] )
        )
    )
RETURN
    CALCULATE (
        MAX ( SampleData[value] ),
        FILTER (
            SampleData,
            SampleData[connection_id] = earlier(SampleData[connection_id])
                && SampleData[date] = MaxNonBlankDateForConnection
        )
    )

[SpartaBI · Accepted solution]

@kostaszogo21 

Last non-blank value =
VAR _id = 'Table'[connection_id]
VAR _last_availble_date =
    CALCULATE (
        MAX ( 'Table'[date] ),
        REMOVEFILTERS ( 'Table' ),
        'Table'[connection_id] = _id,
        'Table'[value] <> BLANK ()
    )
VAR _result =
    CALCULATE (
        MAX ( 'Table'[value] ),
        'Table'[date] = _last_availble_date,
        'Table'[connection_id] = _id,
        REMOVEFILTERS ()
    )
RETURN
    _result

[kostaszogo21]

It works as well! Thanks SpartaBI

[moizsherwani · Accepted solution]

@kostaszogo21  here is my solution, I am sure there are more efficient ways which I will be happy to learn from my fellow experts / super users. I have also attached the sample pbix file for your reference.

LastNonBlankValue = 
VAR MaxNonBlankDateForConnection =
    CALCULATE (
        MAX ( SampleData[date] ),
        FILTER (
            SampleData,
            SampleData[value] <> ""
                && NOT ( ISBLANK ( SampleData[value] ) )
                    && SampleData[connection_id] = EARLIER ( SampleData[connection_id] )
        )
    )
RETURN
    CALCULATE (
        MAX ( SampleData[value] ),
        FILTER (
            SampleData,
            SampleData[connection_id] = earlier(SampleData[connection_id])
                && SampleData[date] = MaxNonBlankDateForConnection
        )
    )

[kostaszogo21]

Thanks so much Moiz!!!