How to create a Product status column

[Jouni900]

Hi,

I have data on multiple KPIs on a product level and to analyse the performance I would like to look separately and together how existing products are performing, what is the contribution of the new products and what is the impact coming from discontinued products. I would like to create a column called product status which would have information Existing, New or Discontinued based on whether the product has had any volume or revenue in the comparable period a year ago.

An example about the data that I have is described below form and I would like to use either revenue or volume as a criteria for the product status. How would you propose me on going with this?

Product	Month	Year	Revenue	Volume
1234	1	2024	5	3
1234	1	2023
1235	3	2024
1236	3	2023	5	3
1237	3	2024	4	3
1237	3	2023	3	2

[v-xinc-msft]

Thank you for the reply from MFelix !

Hi @Jouni900 ,

Did the reply MFelix offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

If the above ones still not help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

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

[MFelix · Accepted solution]

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