sum quantities over different time periods

[rodrigoestrella]

Hello ! 

I have to calculate stock adjustments over sales in several stores of a supermarket in the period between the last stock count and today. The thing is that the stock count date is different for each store, and I would also like to decompose the adjustments by product category. 

I managed to make a measure that calculates them, but when I want to calculate the ratio aggregated by location the measure is not working.

The data looks like this

store_id	adjustment_date	product_id	adjustmentCause_id	Quantity	amount
2	2020/08/20	63	3	1	$3.00

The stock date table has two columns store_id and stock_date. And the sales table looks like this.

store_id	date	product_id	Quantity	amount
2	2020/08/20	63	12	$36.00

The measures I managed to do are this ones.

adjusment = CALCULATE(SUM(merms[amount]), FILTER('Datetable', 'Datetable'[date] > MAX(Stock_date[store_stockdate])))

sales = CALCULATE(SUM(sales[sales_amount]), FILTER('Datetable', 'Datetable'[date] > MAX(Stock_date[store_stockdate])))

ratio = DIVIDE([adjustment], [sales], 0 )

The thing is that when I aggregate by location the date that the measures uses is the max of the stock dates of the stores in that location, not the one that corresponds to each store.

I would really appreciate if you can help me out. Thanks in advance !

Rodrigo

[DataInsights · Accepted solution]

@rodrigoestrella, try this measure:

Adjustments = 
SUMX (
    Merms,
    IF ( Merms[adjustment_date] >= RELATED ( DimStore[stock_date] ), Merms[amount] )
)

Data model:

By store_id:

By location:

[rodrigoestrella]

Thanks a lot !!!!! It worked perfectly !!

[rodrigoestrella]

Thanks a lot !!!!! It worked perfectly !!