Calculating Status based on Two Dynamic Dates

[ttcalendar]

Hi,

I would like to create a DAX measure to to define the customer sales status based on their last order date.

We have 4 statuses:

Active:  Last Order Date between Today and Last 12 months

Passive: Last Order Date between 12 months ago and 24 months ago

Dormant: Last Order Date greater than 24 months ago

Inactive: Never placed an order

I have created a measure for Last Order Date: LASTDATE(SalesOrders[CreatedDate])

In SQL I have this created as per below, but not sure how to translate into a DAX formula.

CASE
WHEN LastOrder.EffectiveDate between dateadd(month,-24,getdate()) and dateadd(month,-12,getdate()) THEN 'Passive'
WHEN LastOrder.EffectiveDate between dateadd(month,-12,getdate()) and getdate() THEN 'Active'
WHEN LastOrder.EffectiveDate < dateadd(month,-24,getdate()) THEN 'Dormant'
WHEN LastOrder.EffectiveDate IS NULL THEN 'Inactive'
END AS CRMtier,

Many thanks,

J

[ERD]

Hi @ttcalendar ,

you can use SWITCH and DATEADD functions.

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