cancel
Showing results for
Did you mean:

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.

Frequent Visitor

## Calculated column with MAX value

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
1 ACCEPTED SOLUTION
Super User

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

4 REPLIES 4
Super User

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

Frequent Visitor

Thx tamerj1, that did produce the same result as my calculation did.

Now I have two ways to do this 🙂

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors