Reply
Regular Visitor
Posts: 22
Registered: ‎01-19-2018
Accepted Solution

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 tiPicture1.jpgme date so 2 statuses could take place on the same day. any help is much appreicated. 


Accepted Solutions
Super User
Posts: 10,430
Registered: ‎07-11-2015

Re: Latest Status

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


View solution in original post


All Replies
Super User
Posts: 10,430
Registered: ‎07-11-2015

Re: Latest Status

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

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Regular Visitor
Posts: 22
Registered: ‎01-19-2018

Re: Latest Status

Hi Greg,

 

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

Super User
Posts: 10,430
Registered: ‎07-11-2015

Re: Latest Status

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Latest Status

Hi @JasonG-BI,

 

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

Latest-Status

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Latest Status

Hi @Greg_Deckler,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.