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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Are you looking for a sql query or a dax?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Try this DAX,

         

        Table 2 = SUMMARIZE('Table','Table'[Date],"Max",CALCULATE(max('Table'[Rank])))
         
        Thnx
  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    Hi davidibi4524 ,

    Do you need a measure? If so, you can use something like this one:

    Measure = 
VAR currentCustomer = MAX('T'[Customer])
RETURN
CALCULATE(
    MAX('T'[OrderDate]),
    'T'[Customer] = currentCustomer)

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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