cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasonG-BI Regular Visitor
Regular Visitor

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

5 REPLIES 5
Super User
Super User

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

 

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

JasonG-BI Regular Visitor
Regular Visitor

Re: Latest Status

Hi Greg,

 

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

Super User
Super User

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

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

v-jiascu-msft Super Contributor
Super Contributor

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
v-jiascu-msft Super Contributor
Super Contributor

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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 317 members 3,353 guests
Please welcome our newest community members: