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

lennardheyder's avatar
lennardheyder
Frequent Visitor
4 years ago

Hey Vahid, I attached a table of the status quo and the target table. Thanks for the hint!

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
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