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.
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 )RETURNIF ( COUNTROWS ( T3 ) = 2, "Repeat", "Non-Repeat" )
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()
Didn't quite work - it flagged all shortages instead of just the latest occurrence.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.