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