First 12 months of sales : problem at higher hierarchy level

[Raph]

Hello,

I wanted to retrieve the total sold quantity of the first 12 month of each product. This command worked well at product level : 

CALCULATE([Total Quantities],DATESINPERIOD('Sales'[Date],MIN('Sales'[Date]),12,MONTH))

But in my matric, when I want to retrieve the subtotal at category level, this formula doesn't calculate the sum of the 12 first months of sales of each product but the sum of the sales of all products starting from the first transaction in this category + 12 months.

So I thought this could be solved by iterating the table product  : 

SUMX('PRODUCTS,CALCULATE([Total Quantities],DATESINPERIOD('Sales '[Date],MIN('Sales'[Date]),12,MONTH)))

Unfortunately I have the exact same problem : good result at product level but not at the category level.

Thank you very much for your help.

Raphaël

[jameszhang0805]

Hi Raph :

       Try like below codes :

The Date in DATESINERIOD function is Date[Date] not Sales[Date] 

[amitchandak]

@Raph , Try like

SUMX(values('PRODUCTS'[Product]),CALCULATE([Total Quantities],DATESINPERIOD('Sales'[Date],MIN('Sales'[Date]),12,MONTH)))

or

SUMX(Summarize('PRODUCTS'[Product],"_1",CALCULATE([Total Quantities],DATESINPERIOD('Sales'[Date],MIN('Sales'[Date]),12,MONTH))),[_1])

[Raph]

Thank you very much for your answer @amitchandak but the 2 commands return exatcly the same result I had before... strange.