Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
11 |