product Life cycle in Power BI

[Sagarkansal]

Hi all,

I have sales data in of different agricultural products with sales in SALES AMOUNT, which is as following in the picture.

Zone	State	Depot	District	Crop	Product	Sales Amount
NZ	HP	Shimla	Shimla	Wheat	Gol	12
CZ	MP	Gwalior	Gwalior	Wheat	Gol	12
SZ	AP	Nellore	Nellore	Chilly	Jwala	55
SZ	AP	Nellore	Nellore	Rice	5566	55
SZ	AP	Nellore	Nellore	Rice	5566	55
SZ	AP	Warangal	Warangal	Rice	5566	55
NZ	HP	Shimla	Shimla	Chilly	Jwala	10
CZ	MP	Gwalior	Gwalior	Wheat	Gol	10
SZ	AP	Nellore	Nellore	Rice	5566	55
SZ	AP	Nellore	Nellore	Chilly	Jwala	55

Now when I create Pivot table of the above considering the columns in EXCEL between PRODUCT and SALES AMOUNT, the result is as follows

	Year
Product	2016	2015	2014	2013	2012	2011	2010	2009	2008	2007	2006	2005	2004	2003	2002	2001	Grand Total
Jwala											10	10	10				30
Gol	17			10	10												37
7029									10								10
5566	10	10	20	20		55	22							10	10		157
2288										10							10
1121								10								10	20
Grand Total	27	10	20	30	10	55	22	10	10	10	10	10	10	10	10	10	264

I wish to know the Product Life cycle, The conditions for the PLC are as follows:

This report will show summation of above data intp three categories Launch, Peak and Post-peak.

Considering reference year as X, any product sold for the first time in X or X-1 or X-2 will be considered as "Launch product".

Similarly any product sold for the first time in X-6 or X-5 or X-4 or X-3 will be considered as "Peak Product"

And, Any product sold for the fist time in X-7 or earlier will be considered as "Post-peak Product"

The categorization of a product is dynamic in nature and will depend on the year when the report is run

Now considering the reference year is 2016,  I wish to know the sales revenue from each of the "LAUNCH, PEAK, POST Peak" periods in the year 2016,

hence it will be as follows

The above I have done manually in excel. I wish that these calculations to be done by the program in Power BI when I choose the date/ year slicer and consequently make graphs based on the values( 1. Pie chart on Launch, Peak, Post peak, 2. PLC curve to show the products on the curve.)

I dont know how it can be achieved. 

[CheenuSing · Accepted solution]

Hi @Sagarkansal

Try the following steps, I created for Launch alone.

1. Create a summary table using 

    Launch = SUMMARIZE(Sales,Sales[Product], "FirstSold",Min(Sales[Year]))

    The table name will be called Launch with column names Product and FirstSold

    Link the product from sales fact and product from this table

2. Create a column called FirstSold in the sales fact table

    as   FirstSold = Related(Launch[FirstSold])

3. Create a year Table which contains the years from 2001 to 2016 and Year as a column name.

    Do no link this to your SalesFact Table.

4. Create a measure called 

    Launched Products = Calculate(
                                                    ( distinctcount(Launch[Product])),
                                                                               FILTER(Launch,
                                                                           COUNTROWS(FILTER(VALUES(Year[Year]),
                                                                                           Launch[FirstSold] >= ('Year'[Year]) - 2 &&
                                                                              Launch[FirstSold] <= ('Year'[Year])
                                                                      ))
                                                                    > 0)
                                                                 )

5. Create a measure SumSales = sum(Salesfact[Sales])

6 Create the measure 

                    LaunchedProductSales = Calculate(
                                                                                 ( [SumSales] ),
                                                                                                 FILTER(Sales,
                                                                                         COUNTROWS(FILTER(VALUES(Year[Year]),
                                                                                                                 Sales[Year] >= ('Year'[Year]) - 2 &&
                                                                                                                  Sales[Year] <= ('Year'[Year])
                                                                                                       ))
                                                                                                        > 0)
                                                 )

7. Display the measures using the card visulaisation and it should match your data.

8.  Create similarly the measures for PeakProducts and Post-Peak Products by altering the year comparison condition.

9.  Below is a screen shot of the output with your data posted.

If this works for you please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[CheenuSing · Accepted solution]

Hi @Sagarkansal

Try the following steps, I created for Launch alone.

1. Create a summary table using 

    Launch = SUMMARIZE(Sales,Sales[Product], "FirstSold",Min(Sales[Year]))

    The table name will be called Launch with column names Product and FirstSold

    Link the product from sales fact and product from this table

2. Create a column called FirstSold in the sales fact table

    as   FirstSold = Related(Launch[FirstSold])

3. Create a year Table which contains the years from 2001 to 2016 and Year as a column name.

    Do no link this to your SalesFact Table.

4. Create a measure called 

    Launched Products = Calculate(
                                                    ( distinctcount(Launch[Product])),
                                                                               FILTER(Launch,
                                                                           COUNTROWS(FILTER(VALUES(Year[Year]),
                                                                                           Launch[FirstSold] >= ('Year'[Year]) - 2 &&
                                                                              Launch[FirstSold] <= ('Year'[Year])
                                                                      ))
                                                                    > 0)
                                                                 )

5. Create a measure SumSales = sum(Salesfact[Sales])

6 Create the measure 

                    LaunchedProductSales = Calculate(
                                                                                 ( [SumSales] ),
                                                                                                 FILTER(Sales,
                                                                                         COUNTROWS(FILTER(VALUES(Year[Year]),
                                                                                                                 Sales[Year] >= ('Year'[Year]) - 2 &&
                                                                                                                  Sales[Year] <= ('Year'[Year])
                                                                                                       ))
                                                                                                        > 0)
                                                 )

7. Display the measures using the card visulaisation and it should match your data.

8.  Create similarly the measures for PeakProducts and Post-Peak Products by altering the year comparison condition.

9.  Below is a screen shot of the output with your data posted.

If this works for you please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[Sagarkansal]

Hi @CheenuSing The year table needs to be dynamic, not static( not only till 2016 but updated automatically accordingly to sales date , which means it will change each year and 2016 was only a reference year. We could have taken 2008 as reference year also and next year 2017 can be reference year

[Sagarkansal]

Hi @CheenuSing

there are 2 error shown when I create measure for Launch Products. WHen the syntax is

1. Launched Products = Launch = (CALCULATE(DISTINCTCOUNT(Launch[Product]),FILTER.........

i.e, when there is no closing bracket in Calculate formula after (Launch[Product]), then PBI says the rror is

ERROR: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

2. When the syntax is as you said as: 

Launched Products = Launch = (CALCULATE(DISTINCTCOUNT(Launch[Product])),FILTER

the error shown is as follows:

The syntax for ')' is incorrect. (DAX(Launch = (CALCULATE(DISTINCTCOUNT(Launch[Product])),FILTER(Launch,COUNTROWS(FILTER(VALUES('YEAR'[Year]),Launch[FirstSold]>=('YEAR'[Year])-2&&Launch[FirstSold]<='YEAR'[Year]))>0))))).

[CheenuSing]

Hi @Sagarkansal

It should be syntax issue .

The original formula I gave was

 Launched Products = Calculate(
                                                    ( distinctcount(Launch[Product])),
                                                                               FILTER(Launch,
                                                                           COUNTROWS(FILTER(VALUES(Year[Year]),
                                                                                           Launch[FirstSold] >= ('Year'[Year]) - 2 &&
                                                                              Launch[FirstSold] <= ('Year'[Year])
                                                                      ))
                                                                    > 0)
                                                                 )

It is not like Launched Products =  Launch = Calculate ( .....

Please check.

As for how it will work with other dimensions, it depnds on the data model. Share the full data model.

Cheers

Cheenusing

[Sagarkansal]

@CheenuSing....

1. PLease find below the full data model. Here YEAR COLUMN is just the YEAR() value of the date column.

2. The PBI should return the Sum of sales value from REFERENCE Year only, following the condition that the product was launched only within the date range according to  PLC terms as of Launch, PEak, Post peak periods.

District	Crop	HY_Var	Product	SKU	Date	Year	Sales Volume	Sales Amount	Rebates_Disc	Net Amount	NRV	FLTC	Gross Contribution	Marketing Overhead	Net Margin
Shimla	Wheat	HY	Gol	Dhalli	10-Jun-16	2016	10	12	3	9	0.9	0.45	0.5	0.09	0.36
Gwalior	Wheat	HY	Gol	Gwalior	12-Oct-16	2016	20	12	3	9	0.5	0.225	0.2	0.045	0.18
Nellore	Chilly	Var	Jwala	XYZ	29-Dec-11	2011	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Nellore	Rice	HY	5566	XYZ	13-Oct-10	2010	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Nellore	Rice	HY	5566	XYZ	13-Oct-14	2014	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Warangal	Rice	HY	5566	XYZ	13-Oct-13	2013	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Shimla	Chilly	Var	Jwala	Dhalli	10-Jun-12	2012	22	10	2.5	7.5	0.3	0.170455	0.2	0.034090909	0.14
Gwalior	Wheat	HY	Gol	Gwalior	12-Oct-13	2013	22	10	2.5	7.5	0.3	0.170455	0.2	0.034090909	0.14
Nellore	Rice	HY	5566	XYZ	29-Dec-14	2014	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Nellore	Chilly	Var	Jwala	XYZ	13-Oct-15	2015	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Nellore	Rice	HY	5566	XYZ	13-Oct-16	2016	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Warangal	Rice	HY	5566	XYZ	13-Oct-13	2013	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Warangal	Rice	HY	1121	XYZ	13-Oct-14	2009	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Shimla	Wheat	HY	Gol	Dhalli	13-Oct-13	2008	22	10	2.5	7.5	0.3	0.170455	0.2	0.034090909	0.14
Gwalior	Wheat	HY	2288	Gwalior	10-Jun-12	2007	22	10	2.5	7.5	0.3	0.170455	0.2	0.034090909	0.14
Nellore	Chilly	Var	Jwala	XYZ	12-Oct-13	2006	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Nellore	Chilly	Var	Jwala	XYZ	29-Dec-14	2005	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Nellore	Chilly	Var	Jwala	XYZ	13-Oct-15	2004	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Nellore	Rice	HY	5566	XYZ	13-Oct-16	2003	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Nellore	Rice	HY	5566	XYZ	13-Oct-13	2002	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75
Warangal	Chilly	Var	Jwala	XYZ	13-Oct-13	2001	22	55	13.75	41.25	1.9	0.9375	0.9	0.1875	0.75

[Sagarkansal]

@CheenuSing The Above PIE chart, TILE Charts were obtained after coding according to your steps.

There are 2 major issues that are faced now:

1. The Sums of sales amount  shouldreturn from the sales amount of REFERENCE YEAR only. Whereas, PBI is summing the sales amount of the previous years from reference year, within  year range of the Launch, Peak and post peak periods (which is not required).

2. If I am working on INDIVIDUAL PRODUCTS

The product can be only in one stage, EIther LAunch, PEAK or Post PEAK (not all the 3) with reference to the REFERENCE year in its PLC. How can we achieve that, considering the following:

a. If product was introduced before 7 years with respect to the REFERENCE year, i.e is in POST peak period currently, then the visualizations of Peak and LAunch category should not be shown

b. If product was introduced between 3-4 years earlier to REFERENCE year, ie product is in PEak period currently, It should not show its vizualization of Post peak and Launch

c. If product was introduced within 3 years from reference year, ie product is in Launch period, it shouldnot show its vizualizations of POST PEAK and PEAK periods

3. If I am working on whole year basis, considereing all the products, then the 3 segments Launch, Peak, Post peak can be shown.

How to show both the points 2 & 3 in PBi and switch between the two whne required in vizualizations on YEar and Product level

[Sagarkansal]

@CheenuSing @v-yuezhe-msft

For reference year, eg. 2016
Launch category sales: As no product was launched in 2016-14(3 years), hence, any contribution from these year to sales of 2016 is not there, hence LAunch product category value is 0
PEAK Category sales: As products 1 and 4 were launched in the range of years 2013-2010 (4 years), hence the products are currently in Peak period of PLC, hence thier sales amount will be the sales amount from YEAR 2016 NOT sum of sales in period 2013 -2010. Hence, PEak period sales is 8+7=15 ,ie. sum of the sales in year 2016
Post Peak category sales: will be sales amount of the remaining products which were sold earlier than 2010, and the sales values will be from year 2016(reference year), i.e 0+0+8+8+13+14+16= 59

Similarly if we would consider year 2010 as reference year,
Launch category sales: will be considered if the products were launched in the period 2010-2008 (3 years), and the sales amount will be considered of year 2010 ie. 9+11+8+10+11+13=52
Peak category sales: will be considered if the products were launched in the period 2007-2004 ( years), but sales before 2006 are not given hence we will consider all sales in peak period sales, and and the sales amount will be considered of year 2010 ie. 13+16+16=45

POST PEAK CATEGORY SALES: As no product was introduced before 2004, hence no sales amount for 2010, from the year 2010 will be considered.

[CheenuSing]

Hi @Sagarkansal

I have solved your problem. But before I send you I need the data table in excel as shown in the picture uploaded to one drive and share the link.

It is midnight now in Singapore.

Cheers

CheenuSing

[Sagarkansal]

@CheenuSing Below is the link for the file on Onedrive.

https://1drv.ms/x/s!AvqPBdmOr62IhEhI84YL0G6s1kVl

Kindly share the link of file here.

Also share the Step wise procedure.

How can I give you kudos? Please tell

[CheenuSing · Accepted solution]

Hi @Sagarkansal

Get Set and GO!!!!!!!!!!!!!

The steps file is uploaded as word document at https://1drv.ms/w/s!ApP3mBZyGaHfgRhcMywbLSFj1vXS

and the pbix file         https://1drv.ms/u/s!ApP3mBZyGaHfgRoDyFexv0L1JlkY     

I will send you a private message for how to give KUDOS. Hey do not forget to give KUDOS to this post.

Cheers 

CheenuSing

[Asantos2020]

Hi!

I've tried having a look at the files, since I find myself needing to have PLC chart, but the files were not available. 

Could you confirm that they are not to be available?

Cheers,

Antonio

[Sagarkansal]

Dear @CheenuSing ji, Pls share the 2 files again. Stepwise and Pbix.

[CheenuSing]

hi @Sagarkansal

I do not have them now. So sorry about that.

Cheers

CheenuSIng

[Sagarkansal]

Dear @CheenuSing Ji.... I am uploading the PBIX and EXCEL files here. Please go through it.

I tried to copy the Launch, Peak and post peak to the PBIX file I had last from you. Following is the link for EXCEL and PBIX files: 

https://1drv.ms/f/s!AvqPBdmOr62IhnuhN6KN7rTlCNK_ 

In PBIX,

a. I wish to filter the First Sold list basede on the year selected (Sales_Data[CY]), which is not filtering. CY means calendar year

b. Upon selecting a CY, I wish to show in 3 different lists, which Products are currently in Launch year, Peak Year or post peak year. No one product can repeat in any other list.  (e.g If One product is in Launch year, it cannot be in Peak or post peak year for that CY selected year)

c. Whean you look at the Edit Queries table, e.g  LaunchProduct yes, then some of the products do not follow the logic to display 1. In some rows, we can see that Launch, Peak and Post peak yesses have 0 as value, whcih is not possible.

[Sagarkansal]

Dear @CheenuSing ... In excel file you may find that last 30-40 rows have only Product Value, Crop value and year values.....Those rows are for determining the exact First sold year for a product and needs to be retained.

[CheenuSing]

Hi @Sagarkansal

I went through your file . I found that the calculated column

LaunchYES = IF([CY]=[FirstSoldCy]&&[CY]-[FirstSoldCy]<=2,1,0) , is wrong. 

The logic says if CY is FirstSoldCY and also (CY - FirstSoldCY) <= 2  .

Say CY = 2017 and firstsoldCY=2016 then the above condition fails and returns 0, where as it should be 1.

I changed the formula as

NewLaunchYES = IF([CY]-[FirstSoldCy]<=2,1,0)

With this

Say CY = 2017 and firstsoldCY=2016 then the above condition will retrun 1.

Try changing your LaunchYes as defined for NewLaunchYES and you should get the right results.

Do get back to me if you need further clarification.

Cheers

CheenuSing

[Sagarkansal]

Dear @CheenuSing....I wish to attain the following 2 points as well

a. In the bottom right window of Power BI, there is a list which shows the product name and First Sold year. I wish to filter the list based on the year selected , which is not filtering. CY means calendar year

b. Upon selecting a CY, I wish to show in 3 different lists, which Products are currently in Launch year, Peak Year or post peak year. No one product can repeat in any other list.  (e.g If One product is in Launch year, it cannot be in Peak or post peak year for that CY selected year)

[Sagarkansal]

Dear @CheenuSing..... I have achieved the first point (a. to filter the first sold list)

Help me in showing the products/product name according to 3 list of Launch, peak and post peak year products name.

[CheenuSing]

Hi @Sagarkansal

This is quite straight forward. Create a table with Product from Sales_Data.

For Launched Products set the visual filter LaunchYes = 1

For PeakProduct Sales set the visual filter PeakYes = 1

For PostPeakProduct Sales set the viausl filter PostPeakYes = 1

Cheers

CheenuSing

[Sagarkansal]

Dear @CheenuSing...I have solved both the parts. Thanks for support.