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

Hi ronald0 

do you have a date table? How are you planning to present the result? Shall it be dynamic based on a selected year/month?

ronald0's avatar
ronald0
Frequent Visitor
3 years ago

Hi tamerj1  yes i gave a data table.

Should it be dynamic ,and can be filtered by months of this year. I think to order the result by years and by months

thanks,

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    ronald0 

    Sorry for the late response. Is the yearmonth column a date data type or text data type?

    • 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