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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
slav84
Helper I
Helper I

How to Determine latest occurrence of value?

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

slav84_1-1621499995319.png

 

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:

 

OrderIdDateAmountOccuranceLastOccurance
100011/5/202151Yes
100021/6/202161Yes
100031/7/202171No
100031/8/2021102Yes
100041/9/202141Yes
100051/10/202171No
100051/11/202142No
100051/12/2021103Yes
100061/13/2021631Yes
100071/14/2021431Yes
100081/15/2021231Yes
100091/16/2021561No
100091/17/2021602Yes
100101/18/2021431Yes
100111/19/2021211Yes

 

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

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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

 

 

SU18_powerbi_badge

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.

 

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

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.

 

Picture2.png

 

https://www.dropbox.com/s/2uur383m43k2t18/slav84.pbix?dl=0 

 

 

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.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 

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


Go to My LinkedIn Page


AlB
Super User
Super User

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

 

 

SU18_powerbi_badge

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.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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