Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
10 | |
10 | |
9 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |