Dynamic column based on slicer

[bkr]

Hi all,

I have a list of products with its launch date (when it was introduced) and the type of introduction, whether it is replacement of an existing product or supports additional growth:

Product	Launch	Type
A	01.01.2015	Replacement product
B	20.06.2017	Growth product
C	15.09.2022	Replacement product
D	30.12.2023	Replacement product
E	08.05.2019	Growth product

Second, I have a table of sales per year:

Product	Year	Sales
A	2023	10
B	2023	20
C	2023	30
D	2023	15
E	2023	25

Now I'd like to display the sales in that year, categorized by product Type.
However, I'd like to add a category "Old" considering all products that have a Launch date before a certain year (given by a slicer) as old products for which the categorization does not apply.

E.g. I set that slicer to 2018, then the expected result would be

Sales	Category
25	Growth
45	Replacement
30	Old

I prepared a disconnected date table and also a disconnected table with one column with the 3 categories but now I'm stuck.

Any help is highly appreciated, thanks!

[Daniel29195]

@bkr 

OUTPUT : 

step1 : 

create a calculated table, with the added "old" row. 

step2 : 

link the new created table to product table

step 3 : 

create the slicer as below : 

step 4 : 

create the measure : 

Measure 3 = 
var t =  VALUES(product_type[Type])
var y =  year(MAX(dimdate[Date]))
var ad = 
ADDCOLUMNS(
    t,
    "sales" ,
    SWITCH(
        TRUE(),
        SELECTEDVALUE(product_type[Type]) = "Old" , 
        CALCULATE(
            SUM(factsales[Sales]),
            factsales[Year] <= y , REMOVEFILTERS(product_type[Type])
         
        )
        ,
        CALCULATE(SUM(factsales[Sales]))
    )
)

RETURN  
SUMX(
    ad,
    [sales]
)

Let me know if it works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[bkr]

Hi Daniel,

thanks for your reply. This almost works as intended.

Your solution nicely works out how much sales fall into the "old" category. It also shows how many of the "old" products are replacement products and how many are growth products.
However, I'd like to show how many of the "not old" products are replacement or growth.
In the example given above, the total should always add up to 100

>E.g. I set that slicer to 2018, then the expected result would be

Sales	Category
25	Growth
45	Replacement
30	Old

means: Out of the 100 bucks sales in 2018, I made 30 bucks with products older than 2018. Out of the remaining 70 bucks sales, 25 come from growth products introduced in 2018 or later and 45 come from replacement products introduced in 2018 or later.

I believe it's only a small step from your solution to what I need, but I'm still not able to fix the rest myself.

Thanks a lot!