Number of Inactive, Lost accounts by Month

[PrathameshPC]

Hi,

I am trying to calculate number of NEW, ACTIVE, INACTIVE, LOST Accounts in a bar chart by Months.

The criteria for status is (If we take a Month e.g. Aug 21 as reference)

NEW = Made first purchase within 30 days of the reference Month

ACTIVE = Made last purchase which falls within 30 days of the reference month

INACTIVE = Made last purchase which is greater than 30 days but less than 90 days

LOST = Made last purchase which is greater than 90 days from the reference month.

I am struggling with showing the Inactive and lost accounts as the thresholds are greater than a period of a month.

[Saravanan_Desingh]

Please post some sample data and expected output

[PrathameshPC]

Sample Data with 2 accounts and 1 Product

Account Name	Sales	OrderDate	Product
ABC	452.5405	01-01-2021	A
ABC	790.4801	01-25-2021	A
XYZ	643.166	01-15-2021	A
XYZ	871.1252	02-24-2021	A
XYZ	913.1576	03-12-2021	A
XYZ	61.35091	04-18-2021	A
XYZ	686.8715	05-05-2021	A

Expected output (attached)

[PrathameshPC]

Account ABC should be shown as

Active in Jan

Inactive in Feb and Mar

lost in Apr

if an Account is lost it should not be shown lost in the next month

XYZ will be active throughout the months