cancel
Showing results for
Did you mean:
Frequent Visitor

How to Determine latest occurrence of value?

Hi there,
I have table with Id, Date and Amount.

As you can see the OrderId repeats sometimes and what i need to find is latest occurance. For example, OrderId 10005 happens 3 times and i need to mark the last accourance. So ultimately in the same table i would like to get something like this:

 OrderId Date Amount Occurance LastOccurance 10001 1/5/2021 5 1 Yes 10002 1/6/2021 6 1 Yes 10003 1/7/2021 7 1 No 10003 1/8/2021 10 2 Yes 10004 1/9/2021 4 1 Yes 10005 1/10/2021 7 1 No 10005 1/11/2021 4 2 No 10005 1/12/2021 10 3 Yes 10006 1/13/2021 63 1 Yes 10007 1/14/2021 43 1 Yes 10008 1/15/2021 23 1 Yes 10009 1/16/2021 56 1 No 10009 1/17/2021 60 2 Yes 10010 1/18/2021 43 1 Yes 10011 1/19/2021 21 1 Yes

This will tell me if the value for that order is last or not.
How do i do this using Dax?

1 ACCEPTED SOLUTION
Super User III

Hi @slav84

Create a calculated column:

``````Last occurence =
VAR lastDate_ = CALCULATE(MAX(Table1[Date]), ALLEXCEPT(Table1, Table1[OrderId]))
RETURN
IF(lastDate_ = Table1[Date], "Yes", "No")``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

3 REPLIES 3
Community Champion

Hi, @slav84

Please check the below picture and the sample pbix file's link down below.

I tried to create a measure for a visualization.

All measures are in the sample pbix file.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Super User III

Hi @slav84

Create a calculated column:

``````Last occurence =
VAR lastDate_ = CALCULATE(MAX(Table1[Date]), ALLEXCEPT(Table1, Table1[OrderId]))
RETURN
IF(lastDate_ = Table1[Date], "Yes", "No")``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Frequent Visitor

Thank you both.
The answer @AlB provided is exactly what i was looking for.

Announcements

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.