Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sagarkansal
Helper I
Helper I

product Life cycle in Power BI

Hi all,

 

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

ZoneStateDepotDistrictCropProductSales Amount
NZHPShimlaShimlaWheatGol12
CZMPGwaliorGwaliorWheatGol12
SZAPNelloreNelloreChillyJwala55
SZAPNelloreNelloreRice556655
SZAPNelloreNelloreRice556655
SZAPWarangalWarangalRice556655
NZHPShimlaShimlaChillyJwala10
CZMPGwaliorGwaliorWheatGol10
SZAPNelloreNelloreRice556655
SZAPNelloreNelloreChillyJwala55

 

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                
Product2016201520142013201220112010200920082007200620052004200320022001Grand Total
Jwala          101010   30
Gol17  1010           37
7029        10       10
556610102020 5522      1010 157
2288         10      10
1121       10       1020
Grand Total27102030105522101010101010101010264

 

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

 

matrix post peak.png

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. 

2 ACCEPTED SOLUTIONS
CheenuSing
Community Champion
Community Champion

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.

Capture.GIF

 

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

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

23 REPLIES 23
CheenuSing
Community Champion
Community Champion

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.

Capture.GIF

 

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

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

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))))).

 

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@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.

 

DistrictCropHY_VarProductSKUDateYearSales VolumeSales AmountRebates_DiscNet AmountNRVFLTCGross ContributionMarketing OverheadNet Margin
ShimlaWheatHYGolDhalli10-Jun-1620161012390.90.450.50.090.36
GwaliorWheatHYGolGwalior12-Oct-1620162012390.50.2250.20.0450.18
NelloreChillyVarJwalaXYZ29-Dec-112011225513.7541.251.90.93750.90.18750.75
NelloreRiceHY5566XYZ13-Oct-102010225513.7541.251.90.93750.90.18750.75
NelloreRiceHY5566XYZ13-Oct-142014225513.7541.251.90.93750.90.18750.75
WarangalRiceHY5566XYZ13-Oct-132013225513.7541.251.90.93750.90.18750.75
ShimlaChillyVarJwalaDhalli10-Jun-12201222102.57.50.30.1704550.20.0340909090.14
GwaliorWheatHYGolGwalior12-Oct-13201322102.57.50.30.1704550.20.0340909090.14
NelloreRiceHY5566XYZ29-Dec-142014225513.7541.251.90.93750.90.18750.75
NelloreChillyVarJwalaXYZ13-Oct-152015225513.7541.251.90.93750.90.18750.75
NelloreRiceHY5566XYZ13-Oct-162016225513.7541.251.90.93750.90.18750.75
WarangalRiceHY5566XYZ13-Oct-132013225513.7541.251.90.93750.90.18750.75
WarangalRiceHY1121XYZ13-Oct-142009225513.7541.251.90.93750.90.18750.75
ShimlaWheatHYGolDhalli13-Oct-13200822102.57.50.30.1704550.20.0340909090.14
GwaliorWheatHY2288Gwalior10-Jun-12200722102.57.50.30.1704550.20.0340909090.14
NelloreChillyVarJwalaXYZ12-Oct-132006225513.7541.251.90.93750.90.18750.75
NelloreChillyVarJwalaXYZ29-Dec-142005225513.7541.251.90.93750.90.18750.75
NelloreChillyVarJwalaXYZ13-Oct-152004225513.7541.251.90.93750.90.18750.75
NelloreRiceHY5566XYZ13-Oct-162003225513.7541.251.90.93750.90.18750.75
NelloreRiceHY5566XYZ13-Oct-132002225513.7541.251.90.93750.90.18750.75
WarangalChillyVarJwalaXYZ13-Oct-132001225513.7541.251.90.93750.90.18750.75

plc.JPG

@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

@CheenuSing @v-yuezhe-msft

plc 1.png

 

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.

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

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@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

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

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

hi @Sagarkansal

 

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

 

 

Cheers

 

CheenuSIng

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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.

 

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.

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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)

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.

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.