cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Schallst
New Member

Trying to find the last occurrence in a column

I'm trying to identify the last occurrence of a shortage on an item and identify the quantity as "Repeat" or "Non-Repeat"

In the dataset below, I'd like to add a new column that calls out order DEFG - 72 units and order XYZ1 - 108 units as repeat shortages.


OrderDateShortRepeat/Non-Repeat
ABCD1/10/20230 
DEFG1/9/202372 
HIJK1/8/202336 
LMNO1/7/20230 
PQRS1/6/20230 
TUVW1/5/20230 
XYZ11/4/2023108 
12341/3/202312 
56781/2/20230 
910A1/1/20230 
3 REPLIES 3
tamerj1
Super User
Super User

@Schallst 

Another way of doing that

=
VAR T1 =
FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
VAR T2 =
TOPN ( 2, T1, 'Table'[Date] )
VAR T3 =
FILTER ( T2, 'Table'[Short] > 0 )
RETURN
IF ( COUNTROWS ( T3 ) = 2, "Repeat", "Non-Repeat" )

Greg_Deckler
Super User
Super User

@Schallst Try:

Column = 
  VAR __Date = [Date]
  VAR __Short = [Short]
  VAR __PreviousDate = MAXX(FILTER('Table',[Date] < __Date), [Date])
  VAR __ShortPrev = MAXX(FILTER('Table', [Date] = __PreviousDate),[Short])
  VAR __Result = IF(__Short > 0 && __ShortPrev > 0, TRUE(), FALSE()
RETURN
  __Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Didn't quite work - it flagged all shortages instead of just the latest occurrence. 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors