Annual sales progression Column chart with new own items added along the year

[Anonymous]

Hello, I am trying to make an annual sales column chart that shows how much we sold throughout the year, thats pretty standard for a simple table, however  we get from our client a table comprising of all the sales they made and this chart computes only items produced by us and along the year we release new items that must be shifted from produced by a 3rd party or produced by us.

Client sales table example

Sales Table
sale date	productid	net sale
s1	p1	$$
s2	p2	$$$
s3	p3	$$$$
s4	p4	$$
s5	p5	$
s6	p6	$$

To do so we use a flag column to mark the items produced by us and use that as a filter in calculate function to get the function to compute only our sales and give us a measure that is used in the chart. But that way if a product is released lets say in july, changing this flag will make the  function  consider that product being ours since forever... so it will be computed as well in the previous months.

Our product identification table

Product Table
productid	pname	Own brand
p1	pname1	1
p2	pname2	1
p3	pname3	1
p4	pname4	0
p5	pname5	1
p6	pname6	1

What I ve been trying to do without success is to make the measure add those products based on the month they were launched under our own brand by using another flag and a column that shows when a new item was added. (so the item is the same, ie. if p2 was a milk carton   and we started producing it it would still count in their system as a milk carton and therefore as p2 but produced by us, and they would take the other brand off the shelfs so all sales past this date would be considered ours.)

To do so I added 2 new columns that show if the item is new and wich date it was introduced

Product Table
productid	pname	Own brand	new product flag	sales date beginning
p1	pname1	1	0	-
p2	pname2	1	1	jul/20
p3	pname3	1	1	jul/20
p4	pname4	0	0	-
p5	pname5	1	0	-
p6	pname6	1	1	nov/20

the original measure is 

total sales = CALCULATE(SUM('sales table'[Net sales]),'Product Table'[own brand] IN { 1 })

So I want to make it consider the date it was introduced so the sales prior to that date counts as other brands (so it is not added in the measure) and sales past that date for that product are added to the measure. O like bellow, the original sales are in green but in july we added newproducts, so I want to count those sales as well and show them, the same happens in november when another product is launched.

But I am having troubles figuring out how to work with dates in DA, anyway to make this work without creating new tables ?

[MFelix · Accepted solution]

Hi @Anonymous ,

Based on the information you gave believe you need to do the following measures:

Sales Own brand = CALCULATE(SUM(Sales[Quantity]), products[Own brand] = 1 ,products[new product flag]  = 0)  

Sales Other Brands = CALCULATE(SUM(Sales[Quantity]), products[Own brand] = 0 )

Sales Other Brands (before date of own brand) = 
SUMX (
    FILTER (
        CROSSJOIN (
            products,
            SUMMARIZE ( sales, Sales[Product], Sales[Quantity], Sales[Sale ID] )
        ),
        Sales[Sale ID] < products[sales date beginning]
            && products[new product flag] = 1
            && products[Own brand] = 1
            && products[productid] = Sales[Product]
    ),
    [Quantity]
)

Sales Own Brands (after date of own brand) = 
SUMX (
    FILTER (
        CROSSJOIN (
            products,
            SUMMARIZE ( sales, Sales[Product], Sales[Quantity], Sales[Sale ID] )
        ),
        Sales[Sale ID] >= products[sales date beginning]
            && products[new product flag] = 1
            && products[Own brand] = 1
            && products[productid] = Sales[Product]
    ),
    [Quantity]
)

Others = [Sales Other Brands] + [Sales Other Brands (before date of own brand)]

Own Brad = [Sales Own brand] + [Sales Own Brands (after date of own brand)]

The last two measure are the ones you need to use on your visualization I just broke it down in 4 measures in order to check the results more accurralty and if needed to be easier to edit the own / other brands part.

Check result in attach PBIX file attach,

Data is:

• 4 products
• 1 quantity per product per month
• 1 own brand
• 1 other brand
• 2 new products one that initiate sales in own brand in July and the other in November.

Has you can see in January until July (when the first item passes to own brand) sales are 1 for own and 3 for other in July to November we have 2 of each after november the chart flips and you get 3 own and 1 others.

[Anonymous]

Omg, thats it ! thank you so much !