I have a pricing database with different products, but the last value of some of them does not necessarily match with the last date of the dataset.
How can I show the last price for the columns that doesn't match to the last date? (otherwise is easy, just using lastdate() function as a filter in the calculate function).
I tried using the lastnonblank() function with no success. (maybe I'm using it wrong?)
In DAX, the LastNonBlank() function will not take the sort order in column, we can't directly take the last value in a column when specifying a static non blank value (like "1") as second argument in this function. Please see:
So in this scenario, I think you need to get use the price as measure to get the last non blank date:
LASTNONBLANK ( 'Date'[Date], CALCULATE ( SUM ('table'[Price] ) ) )
Then compare with the lastdate().
Perhaps you could add an index column to your table that runs from 1 to X. Then you could CALCULATE(LASTNONBLANK(Table[Column], 1), FILTER(ALL(Table), Table[Index] = MAX(Table[Index])))
I haven't tested it but off the top of my head that should always return the value in literal last row in Table[Column] no matter what filter context you're in. If you want filter context to restrict that column just remove the ALL().