Forum Discussion

ronald0's avatar
ronald0
Frequent Visitor
3 years ago
Solved

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: m...
  • tamerj1's avatar
    tamerj1
    3 years ago

    ronald0 
    Please refer to attached sample fuile for more information

    Grouth = 
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
ronald0's avatar
ronald0
Frequent Visitor
3 years ago

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