Forum Discussion
Latest Status
Hi All, I want to show the current stock (inventory level) I have two tables in import mode one a a stock (inventory table) and another a stock status table this status changes as the stock m...
Well, you could do this:
Status = VAR __maxDate = MAX('Stock Status Table'[Status Date]) VAR __stockKey = MAX('Stock Table'[Stock Key]) VAR __table = FILTER('Stock Status Table',[Stock Key]=__stockKey && [Status Date] = __maxDate) VAR __count = COUNTX(__table,[Status Date]) RETURN IF(__count >1 || ISBLANK(__count),"?",MAXX(__table,[Description]))If you add an Index column to your Stock Status Table (with the assumption that your rows are sorted/ordered by Date from least recent to most recent), then you could do this:
Status2 = VAR __maxDate = MAX('Stock Status Table'[Status Date]) VAR __stockKey = MAX('Stock Table'[Stock Key]) VAR __table = FILTER('Stock Status Table',[Stock Key]=__stockKey && [Status Date] = __maxDate) VAR __maxIndex = MAXX(__table,[Index]) VAR __table1 = FILTER(__table,[Index] = __maxIndex) RETURN MAXX(__table,[Description])
Well, you could do this:
Status =
VAR __maxDate = MAX('Stock Status Table'[Status Date])
VAR __stockKey = MAX('Stock Table'[Stock Key])
VAR __table = FILTER('Stock Status Table',[Stock Key]=__stockKey && [Status Date] = __maxDate)
VAR __count = COUNTX(__table,[Status Date])
RETURN
IF(__count >1 || ISBLANK(__count),"?",MAXX(__table,[Description]))
If you add an Index column to your Stock Status Table (with the assumption that your rows are sorted/ordered by Date from least recent to most recent), then you could do this:
Status2 =
VAR __maxDate = MAX('Stock Status Table'[Status Date])
VAR __stockKey = MAX('Stock Table'[Stock Key])
VAR __table = FILTER('Stock Status Table',[Stock Key]=__stockKey && [Status Date] = __maxDate)
VAR __maxIndex = MAXX(__table,[Index])
VAR __table1 = FILTER(__table,[Index] = __maxIndex)
RETURN
MAXX(__table,[Description])