Forum Discussion
Solved
sum quantities over different time periods
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
rodrigoestrella, try this measure:
Adjustments = SUMX ( Merms, IF ( Merms[adjustment_date] >= RELATED ( DimStore[stock_date] ), Merms[amount] ) )Data model:
By store_id:
By location:
3 Replies
- DataInsights
Super User
rodrigoestrella, try this measure:
Adjustments = SUMX ( Merms, IF ( Merms[adjustment_date] >= RELATED ( DimStore[stock_date] ), Merms[amount] ) )Data model:
By store_id:
By location:
- Thanks a lot !!!!! It worked perfectly !!
Thanks a lot !!!!! It worked perfectly !!
