Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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,
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
That worked like a charm. Thanks so much for suggesting. This really helps me! 🙂