How to create a Product status column

Hi Jouni900 ,

 

When you refer the comparable period do you mean the exact same period or in one year period time frame?

 

What I mean by this is if you have a product being analyzed in February 2024, let's assume that this specific product add sales for all 2023 but not in February 2023, altough the comparable period (February) had no sales I assume you want to have this consider as Existing since the product was sold on the previous months (in this case March to December 2023)

 

Another question is that this should be dynamic based on the time frame you are considering so you would have:

Existing - With sales within the last year and current period

New - No sales before the current current period (for all periods)

Discontinued - No sales within 1 year and with sales in current period

 

Is  this correct in term of perception?

 

Depending on the size of your data you can create a measure or a calculated column on your model, both of them have pros and cons.

You can for example create the following measure (be aware I assume you have a calendar and a products dimension, also added another product with sales a lçong time ago.

 

Product Status = 
		CALCULATE(
			VAR Mincalendar = MIN('Calendar'[Date])
			VAR MincalendarPY = MINX(
				DATEADD(
					'Calendar'[Date],
					-1,
					YEAR
				),
				'Calendar'[Date]
			)

			VAR Within1Year = CALCULATE(
				SUM(Sales[Revenue]),
				Sales[Date] <= Mincalendar - 1 && Sales[Date] >= MincalendarPY,
				ALL('Calendar'[Date])
			)
			VAR MoreThan1Year = CALCULATE(
				SUM(Sales[Revenue]),
				Sales[Date] <= MincalendarPY
			)
			VAR NewItem = CALCULATE(
				SUM(Sales[Revenue]),
				ALL('Calendar'[Date])
			)
			RETURN
				SWITCH(
					TRUE(),
					Within1Year = 0 && MoreThan1Year > 0, "Discontinued",
					NewItem = 0, "New",
					Within1Year > 0, "Existing"
				),
			CROSSFILTER(
				'Calendar'[Date],
				Sales[Date],
				None
			)
		)

 

See file attach

Be aware that depeding on your model this solution can have performance issues, has I refered and for making the use of this new column you probably can also use a column at the date level to identify the status