Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## LASTDATE Not working as required

Hi guys, I am trying to retrieve the most recent QTY On Hand for each product in my inventory but the trouble I am having is that for whatever reason, some of these products can be present on multiple order lines of the same order with the same order date and time making it difficult to calculate the most recent QTY On Hand.  Below, is an example of such case.  My formula is the following :

LAST INVENTORY VALUE = VAR X = CALCULATE(MAX(INTRAN00[TXQOH]),LASTNONBLANK(INTRAN00[TRANSACTION DATE],1)) RETURN X

Highlighted in Green is the value I want to retrieve but my formula currently retreives 298.  But , Based on the time reference (TXTIME) the most recent transaction is 7394480 *TXORD#) focusing on the last line (TXLINE) of that order which is 4.

So essentially my formula needs to take into account order time as well as the very last line number in instances such as these.

Any recomendations ?

1 REPLY 1
Super User

Try this:

``````LAST INVENTORY VALUE =
VAR X =
CALCULATE (
MAX ( INTRAN00[TXQOH] ),
FILTER (
INTRAN00,
INTRAN00[TXTIME] = MAX ( INTRAN00[TXTIME] )
&& INTRAN00[TXLINE] = MAX ( INTRAN00[TXLINE] )
)
)
RETURN
X``````

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors