Get the most recent stock value in weekends

[Rickmaurinus]

Dear community,

I'm currently making a dashboard to track an investment portfolio. It consists of several queries. 

There's a calendar, a query with the times that stocks were bought called transactions (with the amount and price), and there's a query with the daily stock values called Allprices. I added a screenshot of how the data looks. 

The values of the stocks don't change in the weekends. So there's only weekdays that mutate. I would like to make a measure in Power BI, that takes the latest known stock value in the weekend. So that on saturdays and sundays, the value would be taken from friday.  Currently, by taking a SUM of the stockprice, I get the values of measure "StockPrice". 

I've tried to come up with a formula to get the values from friday, in the weekends. I've been juggling around with a LASTNONBLANK formula, but don't get it to work. I hope somebody has a suggestion to help me out there. I get the feeling I don't take into account the Dates, or perhaps the other investment funds. 

[v-yuezhe-msft · Accepted solution]

@Rickmaurinus,

Create the measure using DAX below and check if you get expected result.

Teststock2 = 
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( 'Calendar'[Date], 1 ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date]<= MAX('Calendar'[Date])
                && NOT ( ISBLANK ( [Stockedprice] ) )
        )
    )
RETURN
    CALCULATE (
        [Stockedprice],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date]= LastNonBlankDate )
    )

Regards,
Lydia

[Rickmaurinus]

That worked like a charm. Thanks so much for suggesting. This really helps me! 🙂