Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ErikOmni
Helper I
Helper I

Tag customer order based on if order is within 12 months of last order (or after)

Hi!

 

So I have dataset of orderdata that have manipulated a bit. I have defined customers depending of if they are new or existing.

 

New = First order

Existing = not first order

 

However, I would like to break up orders from Existing customers into Active / Reactivated.

 

Active = Order is within 12months of last/latest order

Reactived = Order is more than 12 months since last/latest order

 

Basically, in the Power Query I would like to be able to say "if order is within 12months of last order then 'Active' "

 

Can't figure out how to do it! Dataset looks like this:

 

ErikOmni_0-1673808207329.png

 

 

Any ideas?

 

Thanks!

 

2 REPLIES 2
vicky_
Super User
Super User

If I was to break your problem down there are 3 steps:
1. Find out if the customer is new or returning

2. If they are returning, then what is their previous order date?
3. Is that order in the last 12 months?

 

This site here gives you the formula to get the days since their previous purchase. You can use that and wrap it in an IF( ) condition to get your active or inactive customer tag.

Thanks for replying @vicky_ !

 

 

However, I was a bit unclear on the part that makes this tricky. I want to be able to do YoY comparisions

 

The methodology in the link will not support YoY comparision (as LASTDATE will basically do exactly that..). 

 

So I need to be able to say, for each order, "what was the date of the previous order for this customer? If more than 12 months has passed, then 'Reactivated' " and so on

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.