Forum Discussion

kmansweden2017's avatar
kmansweden2017
Frequent Visitor
3 years ago
Solved

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  
EventDateDelivery_countInvoice_countOrder_countReal_TB1VehicleNumberGet Latest Real_TB1  
20230324111159580008634817418  
20230329000174180008634817418  
20230325000174180008634817418  
MAX date
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi kmansweden2017 

    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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi kmansweden2017 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      Now I have two ways to do this 🙂

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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