topic Trying to find the last occurrence in a column in DAX Commands and Tips

Trying to find the last occurrence in a column

Anonymous — Fri, 12 May 2023 17:10:17 GMT

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

Re: Trying to find the last occurrence in a column

Greg_Deckler — Fri, 12 May 2023 17:23:08 GMT

Anonymous 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

Re: Trying to find the last occurrence in a column

tamerj1 — Fri, 12 May 2023 17:45:39 GMT

Anonymous

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

Re: Trying to find the last occurrence in a column

Anonymous — Fri, 12 May 2023 19:02:10 GMT

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