Latest Status

[JasonG-BI]

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. 

[v-jiascu-msft]

Hi @Greg_Deckler,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @JasonG-BI,

How about a filter? Please refer to the snapshot below.

Best Regards,
Dale

[Greg_Deckler]

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

[JasonG-BI]

Hi Greg,

Thanks for your reply please see attached sample data.Sample Data.PBIX

[Greg_Deckler · Accepted solution]

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])