help with max value

[davidibi4524]

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.

[Anonymous · Accepted solution]

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]

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.

[davidibi4524]

thanks but i need it as a table

[ERD]

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

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]

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

[Anonymous]

Are you looking for a sql query or a dax?

[davidibi4524]

DAX

[Anonymous]

Try this DAX,

Table 2 = SUMMARIZE('Table','Table'[Date],"Max",CALCULATE(max('Table'[Rank])))

Thnx