Forum Discussion

lennardheyder's avatar
lennardheyder
Frequent Visitor
4 years ago
Solved

Group table by max date and categories

Hey everybody,

 

I have a table with a deal ID, a customer, a product and a date (valid until). A customer can have several deals with the same product therefore I need an aggreagegated table which contains only the max date of the product / customer combination, the customer and the product - needs to be somehow an aggregate. Any Idea how to solve this challenge? Guess the Summarize columns / Group by is the right choice!

 

MAX DMT = GROUPBY(DMT_EM_REPORTING,DMT_EM_REPORTING[Key],DMT_EM_REPORTING[HIERARCHY_NM_EN],DMT_EM_REPORTING[ART_NAME_NM],DMT_EM_REPORTING[PRICE_VALID_UNTIL_DATE])   --  this is the standard grouping by the columns of interest!
 
From:
 
CustomerProductDeal IDValid until
AB#131.06.2022
A B#131.12.2022

C

A#131.01.2022

C

A#131.06.2022

 

To:

CustomerProductDeal IDValid until
 A B#131.12.2022

 C

 A#131.06.2022

 

Aggregate table to those lines with the max date.

Need Help
Show and Tell
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, lennardheyder 

    Try to create a calculated table as below:

    Table2 = 
GROUPBY (
    Table1,
    Table1[Customer],
    Table1[Deal ID],
    Table1[Product],
    "Max Date",
    MAXX ( CURRENTGROUP (), MAX ( Table1[Valid until] ) )
)

    or

    Table3 = 
SUMMARIZE( 
    Table1,
    Table1[Customer],
    Table1[Deal ID],
    Table1[Product],
    "Max Date",
    MAX ( Table1[Valid until] ) 
)

    GROUPBY

    SUMMARIZE

     

    Best Regards,
    Community Support Team _ Eason

3 Replies