Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.
Hi,
I am so lost.
I dont really know how I would search for a simular problem since i dont know how to explain it without a visual aid.
I am trying to get the value from the row with the latest date. (Row.2)
Row.1 is the actuall event date (delivery date),
I need to get the latest Real_TB1 in a calculated column Get Latest Real_TB1 (the last column)
Somehow I guess I need to use use MAX EventDate where VehicleNumber = VehicleNumber and then get Real_TB1 from that.
This table has around 300.000 rows
Calculated Col | ||||||||
EventDate | Delivery_count | Invoice_count | Order_count | Real_TB1 | VehicleNumber | Get Latest Real_TB1 | ||
20230324 | 1 | 1 | 1 | 15958 | 00086348 | 17418 | ||
20230329 | 0 | 0 | 0 | 17418 | 00086348 | 17418 | ||
20230325 | 0 | 0 | 0 | 17418 | 00086348 | 17418 |
Solved! Go to Solution.
I guess a little bit of CALCULATE won't harm @Greg_Deckler 😉
Latest Real_TB1 =
MAXX (
TOPN (
1,
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[VehicleNumber] ) ),
'Table'[EventDate]
),
'Table'[Real_TB1]
)
I guess a little bit of CALCULATE won't harm @Greg_Deckler 😉
Latest Real_TB1 =
MAXX (
TOPN (
1,
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[VehicleNumber] ) ),
'Table'[EventDate]
),
'Table'[Real_TB1]
)
Thx tamerj1, that did produce the same result as my calculation did.
Now I have two ways to do this 🙂
@kmansweden2017 Try:
Column =
VAR __MaxDate = MAX('Table'[EventDate])
VAR __MaxDateRealTB1 = MAXX(FILTER(ALL('Table'), [EventDate] = __MaxDate), [Real_TB1])
RETURN
__MaxDateRealTB1
Watch this video if you need more help, especially the last example, Double Lookup.
Thx for the quick respons!
That didnt really work but somehow I manage to get it to work with this.
I will look at the video tomorrow.
Get Latest Real_TB1 =
Var GetDate =
CALCULATE(
MAX( Fact_KobraRetail[EventDate] ),
FILTER ( ALL (Fact_KobraRetail ), Fact_KobraRetail[VehicleNumber] = EARLIER( Fact_KobraRetail[VehicleNumber] ) )
)
Var Result =
CALCULATE(
MAX( Fact_KobraRetail[Real_TB1] ) ,
FILTER( ALL ( Fact_KobraRetail ), Fact_KobraRetail[VehicleNumber] = EARLIER( Fact_KobraRetail[VehicleNumber] ) ) ,
FILTER( ALL ( Fact_KobraRetail ), GetDate = Fact_KobraRetail[EventDate] )
)
RETURN Result
//Kman
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
19 | |
13 | |
10 | |
9 | |
9 |