Last order date by Customer

[atjt217]

Hello, 

I would like to ask for help figuring out which formula to use on my report. I got 2 tables where i need to get the last order date from the order table to be able to see when is the last time our Customer made an order: 

Sample

I appreciate your help! 

[Anonymous · Accepted solution]

Hi @atjt217 ,

All DAX functions are supported for DirectQuery models. However, not all functions are supported for all formula types. Some functions like CALCULATE are supported in measure and query formulas only. For details: DAX Formula Compatibility in DirectQuery Mode

In your scenario, please new a measure rather than creating a calculated column using above formula.

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
CALCULATE (
    MAX ( 'Sheet2$'[Date] ),
    FILTER (
        ALL ( 'Sheet2$' ),
        'Sheet2$'[Customer ID] = SELECTEDVALUE ( 'Sheet2$'[Customer ID] )
    )
)

Then create relationships between the tables.

If I have misunderstood your meaning, please provide your desired output with more details.

Best Regards

Community Support Team _ Polly

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

[VahidDM]

Hi @atjt217 

You can add a new column with dax to the Customer Table with this formula:

LastOrder = 
CALCULATE (
    MAX ( 'Order Table'[Date] ),
    FILTER (
        ALL ( 'Order Table' ),
        'Order Table'[CustomerID] = EARLIER ( 'Customer Table'[CustomerID] )
    )
)

Output:

Sample file attached, please download.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[atjt217]

Hello VahidDM, 

I tried it out but im getting this error message: 

"Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models."