Evolution of the product based on launch date

[Sampath_Santosh]

Hi everyone,
Hope you are doing great.

I have a challenge and want your help.

Here is the sample data.
Product is product name 
Country is country name 
Month is sales for the month
Sales is Amount of Sales 
Launch date is when the product is launced in the market 

Product	Country	Month	Sales	Launch Date
Product x	Germany	01-09-2023	103	01-03-2021
Product x	France	01-01-2021	658	01-01-2021
Product x	Germany	01-02-2021	363	01-03-2021
Product x	France	01-03-2021	585	01-01-2021
Product x	Germany	01-04-2021	908	01-03-2021
Product x	France	01-05-2021	183	01-01-2021
Product x	Germany	01-06-2021	247	01-03-2021
Product x	France	01-07-2021	240	01-01-2021
Product x	Germany	01-08-2021	216	01-03-2021
Product x	France	01-09-2021	685	01-01-2021
Product x	Germany	01-10-2021	590	01-03-2021
Product x	France	01-11-2021	507	01-01-2021
Product x	Germany	01-12-2021	847	01-03-2021
Product x	France	01-01-2022	418	01-01-2021
Product x	Germany	01-02-2022	396	01-03-2021
Product x	France	01-03-2022	129	01-01-2021
Product x	Germany	01-04-2022	282	01-03-2021
Product x	France	01-05-2022	294	01-01-2021
Product x	Germany	01-06-2022	130	01-03-2021
Product x	France	01-07-2022	716	01-01-2021
Product x	Germany	01-08-2022	942	01-03-2021
Product x	France	01-09-2022	318	01-01-2021
Product x	Germany	01-10-2022	866	01-03-2021
Product x	France	01-11-2022	266	01-01-2021
Product x	Germany	01-12-2022	432	01-03-2021
Product x	France	01-01-2023	857	01-01-2021
Product x	Germany	01-02-2023	703	01-03-2021
Product x	France	01-03-2023	200	01-01-2021
Product x	Germany	01-04-2023	382	01-03-2021
Product x	France	01-05-2023	232	01-01-2021
Product x	France	01-06-2023	150	01-01-2021
Product x	Germany	01-03-2021	855	01-03-2021
Product x	France	01-04-2021	297	01-01-2021
Product x	France	01-05-2021	928	01-01-2021

The challenge is I need to show the evolution of product for the last 10 months 
(Evolution : Trend of the product since the launch)


The 1st challenge is :
X axis should have numbers (0,1,2,3,4,5,6,7,8,9,10) - (months completed since launch) where 0 will be 0 sales every time 
Eg : Product X launced in france on "01-01-2021" and sale for the month is 658 , This value should show on 1 of my line chart , while 0 should be always 0.

And second challenge is, on selecting particular month in slicer , i need to show last 10 months sale trend of the products  launched in that last 10 months (Evolution).

Thanks in advance 🙂 

[Sampath_Santosh]

Hi Rena,
Thank you so much for taking time and helping me.
Here i may be more clear , its my fault .
The second challenge was , on selecting a (single selection) month (sales month - Eg:01-09-2023) , I need to consider, in the last 10 months ( 01-12-2022 to 01-09-2023 ) which are the products have been launched and thier trend/Evolution. 
Eg Product X launched on 01-09-2023 , so i should consider this product in my line chart .
product Y launched on 01-10-2022 , I should not consider this product in my line chart .
Similarly Product Z launched on 01-10-2023 , I should not consider this product in my line chart.

Can you please help me in this case ?
Thank you once again for your support.

[v-yiruan-msft]

Hi @Sampath_Santosh ,

I'm a little confused. Could you please provide some specific examples to explain your requirement based on your shared sample data? For example: when select 01-09-2023, which values you want to display on the line chart? The last 10 months ( 01-12-2022 to 01-09-2023 ) is for [launch data] or [Month] field? Thank you.

Best Regards

[Sampath_Santosh]

Hi,
I am just modifying the logic for 5 months instead of 10 months for explination.
Here is sample data with less data and caluclations done in Excel for reference.
----------------------------------------------------Data--------------------------------------

Product	Sales for the Month	Sales	Launch Date	Diff In months from Launch	+1 Month
Product X	01-Dec-22	501	01-Dec-22	0	1
Product X	01-Jan-23	415	01-Dec-22	1	2
Product X	01-Feb-23	309	01-Dec-22	2	3
Product X	01-Mar-23	174	01-Dec-22	3	4
Product Y	01-Jan-23	944	01-Jan-23	0	1
Product Y	01-Feb-23	232	01-Jan-23	1	2
Product Y	01-Mar-23	459	01-Jan-23	2	3
Product Z	01-Oct-22	100	01-Oct-22	0	1
Product Z	01-Nov-22	115	01-Oct-22	1	2
Product Z	01-Dec-22	994	01-Oct-22	2	3
Product Z	01-Jan-23	686	01-Oct-22	3	4
Product Z	01-Feb-23	442	01-Oct-22	4	5
Product Z	01-Mar-23	600	01-Oct-22	5	6

+ 1 month column is done for if 0 is there we should always show 0
------------------------------------------------------------------------------------------------
Condition : 
On selecting a sale for the month of Mar-23 , Eligible products to be shown are : Sale month - 5 months range (Nov'22, Dec'22, Jan'23 , Feb'23,Mar'23).
Here Product X & Y launch dates fall in the range of Nov'22 to Mar'23 so these products to be considerd.
Product Z is launched in Oct'22 so we should not consider that product.

So abobe result is what we need to see in Power BI.
Thank you .
Please let me know if still any quires, i am happy to explain again.
Thanks 🙂 

[v-yiruan-msft · Accepted solution]

Hi @Sampath_Santosh ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a number dimension table

Months completed since launch = GENERATESERIES(0, 10, 1)

2. Create a measure as below to get the sales for last 10 months after launch

Sales for 10 months after launch = 
VAR _sellaunchdate =
    SELECTEDVALUE ( 'Table'[Launch Date] )
VAR _selmonths =
    SELECTEDVALUE ( 'Months completed since launch'[Value] )
RETURN
    IF (
        _selmonths = 0,
        0,
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            FILTER (
                'Table',
                DATEDIFF ( _sellaunchdate, 'Table'[Month], MONTH ) = _selmonths - 1
            )
        )
    )

3. Create a line chart visual

Best Regards