cancel
Showing results 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

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.

 Order Date Short Repeat/Non-Repeat ABCD 1/10/2023 0 DEFG 1/9/2023 72 HIJK 1/8/2023 36 LMNO 1/7/2023 0 PQRS 1/6/2023 0 TUVW 1/5/2023 0 XYZ1 1/4/2023 108 1234 1/3/2023 12 5678 1/2/2023 0 910A 1/1/2023 0
3 REPLIES 3
Super User

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

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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
New Member

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

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