Forum Discussion
Same store sales growth
Hi,
i'm trying to calculate two measure :
The first to find the growth versus last year, only for the same stores opened in both years. The granularity is the month.
Example:
This year:
monthyear store_id sales
| june 23 | 1 | 80 |
| june 23 | 2 | 90 |
| june 23 | 3 | 85 |
| june 23 | 4 | 100 |
Last Year:
monthyear store_id sales
| june 22 | 2 | 85 |
| june 22 | 3 | 70 |
| june 22 | 4 | 75 |
| june 22 | 5 | 70 |
The result should be the total sales for the (2,3,4) stores in 23 over the (2,3,4) in june22 = 275 / 230.
So, it should exclude the stores closed in 22 and open in 23, and the closed in 23 and opened in 22.
The second measure, should calculate the growth including also the new stores opened in 23 that were closed in 22 ( like the store_id=1) but exclude the closed in 23 and opened in 22.
Thanks.
ronald0
Please refer to attached sample fuile for more informationGrouth = VAR SelectedYear = MAX ( 'Table'[Year] ) VAR SelectedYearStores = CALCULATETABLE ( VALUES ( 'Table'[store_id] ), 'Table'[year] = SelectedYear, ALL ( 'Table' ) ) VAR PreviousYearStores = CALCULATETABLE ( VALUES ( 'Table'[store_id] ), 'Table'[year] = SelectedYear - 1, ALL ( 'Table' ) ) VAR CommonStores = INTERSECT ( SelectedYearStores, PreviousYearStores ) VAR SelectedYesrSales = CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = SelectedYear, 'Table'[store_id] IN CommonStores ) VAR PreviousYearSales = CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = SelectedYear - 1, 'Table'[store_id] IN CommonStores ) RETURN SelectedYesrSales - PreviousYearSales
6 Replies
Thanks tamerj1 . I think can be a good solution.
Now i resolved with another solution.
SameStoreSalesThisYear=CALCULATE([sales_this_year];FILTER(VALUES(store[store_id]);IF([Sales_last_year]>0;1)))
SameStoreSalesLastYear=CALCULATE([Sales_last_year];FILTER(VALUES(store[store_id]);IF([SameStoreSalesThisYear]>0;1)))
% Sales = DIVIDE(SSSTY;SSSLY)-1
