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 moves through the system. i.e goes from supplier order, to in stock, to invoiced, to archived. The stock key in the stock table is unique hower there are multiple entries for each status (description) in the stock status table
I just want to show the items that are in stock. I need a solution that works bearing in ming the status date is only a date column not time date so 2 statuses could take place on the same day. any help is much appreicated.
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])
5 Replies
Sample data would really help but basically you are going to use MAX of your date then you just have to figure out the multiple entries on a single day. Perhaps adding an Index to your query?
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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])
Hi Anonymous,
How about a filter? Please refer to the snapshot below.
Best Regards,
Dale
