Help to convert subquery to DAX

[LuisLOCapelari]

Hello, I need help converting this SQL into a DAX measure.
The sql looks for purchases that are customer recovery, that is, a customer who made a purchase, his contract expired, and after a while he returned to being a customer.

[lbendlin]

Don't try to "fight the API".  There is no meaningful translator from SQL to DAX. Please explain your business issue and provide sample data and expected outcome.

[LuisLOCapelari]

I didn't want a translator from SQL to DAX, in the literal sense. I wanted some help to know how to write this SQL in a way that get the same results in Power BI.

This is a dashboard that I'm developing at my job, so I don't think I can share the data.

The company I work for is a site where a teacher can generate tests. He buys credits. Each credit entitles him to enter a question on an exam. When the teacher makes a purchase he has a deadline to use the credits he bought.

I'll try to explain this SQL:
- I look for all purchases of each customer
- For each line (purchase) returned in the SQL, the subquery searches for the last purchase prior to this one and returns the expiration date of that purchase

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[LuisLOCapelari]

Sorry for the late reply and the misunderstanding. Here the link with the sample data: https://drive.google.com/file/d/1XCWUor50r3otSaE5OZYNpHobb3L0gSW2/view?usp=sharing.

The expected result is 171 orders.

Thanks!

[lbendlin · Accepted solution]

Attached is one option to do that.  Make sure the filter changes are acceptable.

[LuisLOCapelari]

It worked out. I had to make some changes, but it helped me a lot. Thank you very much!