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

Hi tamerj1 . It's a text data type.In fact i have a year column and a month column

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
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