Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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" )
@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
Didn't quite work - it flagged all shortages instead of just the latest occurrence.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |