Forum Discussion

davidibi4524's avatar
davidibi4524
Frequent Visitor
5 years ago
Solved

help with max value

hi i have a table from SQL server that contain: customer number ---- order date i need to edit the query to get only the max(order curdate) for each customer. how can i do it? thanks.
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi davidibi4524 ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. I used the following methods separately to get the latest order date:

    1. Create a table visual with Max of Order date

    2. Create a measure to get the latest order date

    Latest order date =
CALCULATE (
    MAX ( 'Prev_forecast'[Order date] ),
    FILTER (
        'Prev_forecast',
        'Prev_forecast'[CUSTNAME] = SELECTEDVALUE ( Prev_forecast[CUSTNAME] )
            && 'Prev_forecast'[ordname] = SELECTEDVALUE ( 'Prev_forecast'[ordname] )
    )
)

    3. Create a calculated table

    TableName = 
SUMMARIZE (
    'Prev_forecast',
    'Prev_forecast'[CUSTNAME],
    'Prev_forecast'[ordname],
    "maxordate", CALCULATE ( MAX ( 'Prev_forecast'[Order date] ) )
)

    Best Regards

ERD's avatar
ERD
Community Champion
5 years ago

davidibi4524 ,

In case of DAX table:

 

TableName = 
ADDCOLUMNS (
    DISTINCT ( 'T'[Customer] ),
    "@latestDate", CALCULATE ( MAX ( 'T'[OrderDate] ) )
)

DAX query:

EVALUATE
ADDCOLUMNS (
    DISTINCT ( 'T'[Customer] ),
    "@latestDate", CALCULATE ( MAX ( 'T'[OrderDate] ) )
)

 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

davidibi4524's avatar
davidibi4524
Frequent Visitor
5 years ago

its looks like close to the solution,because now i need to add some more columns and its didnt work for me. i can only add the first column again (custname)

here is a screenshot:

 

 

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    davidibi4524 , you need to add an existing column or to calculate something?

    If you need to add several columns like 'T'[Customer], then the code will be a bit different:

    TableName = 
ADDCOLUMNS (
    SUMMARIZE('T', 'T'[Customer], 'T-main'[AnotherColumn]),
    "@latestDate", CALCULATE ( MAX ( 'T'[OrderDate] ) )
)

    If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.