Return current value from LastNonBlank by date

[Anonymous]

Hi,

I have a table "DividendStocks" and I need a column or measure like "Shares": 

StockTicker	Date	Purchase	Shares (what I need)
PEP	Jan	0	50
GILD	Jan	0	200
SVK	Jan	100	150
PEP	Dec	50	50
GILD	Dec	200	200
SVK	Dec	50	50

I have tried this calc column but it does not seem to work properly. 

Shares =

var LastNonBlankDate = CALCULATE(MAX(DividendStocks[Date]),FILTER(ALL(DividendStocks),DividendStocks[Date]<=MAX(DividendStocks[Date]) && DividendStocks[Purchase]<>0))

var LastNonBlankST = CALCULATE(MAX(DividendStocks[StockTicker]),FILTER(ALL(DividendStocks),DividendStocks[StockTicker] <>""))

RETURN

CALCULATE(SUM(DividendStocks[Purchase]),

FILTER(ALL(DividendStocks),DividendStocks[Date] = LastNonBlankDate))

Thanks!
Mikkel 

[v-xulin-mstf · Accepted solution]

Hi @Anonymous,

Try measure as:

shares =

CALCULATE(

    SUM(DividendStocks[Purchase]),

     FILTER(

         ALL(DividendStocks),

        DividendStocks[Date]<=MAX('Table'[Date]) &&  DividendStocks[Purchase]<>0 && DividendStocks[StockTicker]=MAX(DividendStocks[StockTicker]) && DividendStocks[StockTicker] <>""))

Here is the output:

Here is the demo, please try it: Return current value from LastNonBlank by date

Best Regards,

Link

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

[parry2k]

@Anonymous it is easier if you add a date dimension in your model and work from it. To add a date dimension, check my blog post and then you can add this measure

Previous Month Shares = CALCULALTE ( SUM ( Table[Purchase] ), PREVIOUSMONTH ( DateTable[Date] ) )  

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡