Accumulated sales with products

[Carlosrpsl]

Hi, 

In my database I have sales by months and products. But not every months I sell same products 

For example:

January

Products: A, B, C

February: 

Products: B, C

In my graphic of accumulated sales in February only show products B and C, in order to I dont have the row of product A

My current formula is:

CALCULATE (
SUM ( Sales[sales] );
FILTER (
Sales;
Sales[Month-Year] <= EARLIER ( Sales[Month-Year] )
&& YEAR ( Sales[Month-Year] ) = YEAR ( EARLIER ( Sales[Month-Year] ) )
&& Sales[prducts] = EARLIER ( Sales[products] )
)
)
)

Any idea?

[Ashish_Mathur · Accepted solution]

Hi,

If in Feb, you only want to see the accumulated sales of B and C (not A because this product did not see in Feb), then follow this process:

1. Create a Calendar Table and build a relationship from the Date column of the Sales Table to the Date column of the Calendr Table
2. In the Calendar Table, extract Year and Month by using the calculated column formulas: Year=Year(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
3. In your visual, drag Year to the filter section and select any Year
4. In your visual drag Month from the Calendar Table and Product from the Sales Table
5. Write these measures

Total Sales = SUM(Sales[sales])

Accumulated Sales = IF(ISBLANK([Total Sales]),BLANK(),CALCULATE([Total Sales],DATESYTD(Calendar[Date],"31/12")))

Drag the Accumulated Sales measure to your visual.

Hope this helps.