Forum Discussion

Lasangi's avatar
Lasangi
Frequent Visitor
2 years ago
Solved

Measure for Same Store Sales Growth

Hi,   Need some help with the below scenario:   I have a sales table with daily sales for different stores. Need to calculate same store sales growth at store level and company level in compariso...
  • AndreDeLange's avatar
    AndreDeLange
    2 years ago

    So the first thing you need to do is ensure that you have the correct week number defined in your D01 DIM Date table. Assuming you're using Power Query, you can add this step to the previous step to ensure you're bringing in the week number starting on a Tuesday:
    = Table.AddColumn(#"Previous Step", "Week of Year", each Date.WeekOfYear([Date],2), Int64.Type)

    Now assuming you have 'F06 FACT Sales' with three columns: Date, Store and Value, with the Date column connected to the Date column in the D01 DIM Date table, you'll need these measures:

    Sales $ =SUM('F06 FACT Sales'[Value])

    SalesLastYearSameWeek# =VAR CurrentWeek = MAX( 'D01 DIM Date'[Week#] )

    VAR CurrentWeekDay = MAX( 'D01 DIM Date'[Day of Week] )

    VAR CurrentYear = MAX( 'D01 DIM Date'[Year] )

    RETURN

    CALCULATE(

    [Sales $],

    FILTER(

    ALL( 'D01 DIM Date'),

    'D01 DIM Date'[Week#] = CurrentWeek&&

    'D01 DIM Date'[Day of Week]= CurrentWeekDay&&

    'D01 DIM Date'[Year] = CurrentYear - 1

    )

    )

     

    Qualifying Sales $ = var include = IF(NOT(ISBLANK([Sales $]))&&NOT(ISBLANK([SalesLastYearSameWeek#])),[Sales $],BLANK())
    return include

    Qualifying Sales $ SameWeekLY:=var include = IF(NOT(ISBLANK([Sales $]))&&NOT(ISBLANK([SalesLastYearSameWeek#])),[SalesLastYearSameWeek#],BLANK())
    return include

    SalesCurrentWeek:=VAR CurrentWeekNumber = MAX( 'D01 DIM Date'[Week#] )
    VAR CurrentYear = MAX( 'D01 DIM Date'[Year] )
    VAR CurrentYearSales = SUMX('D01 DIM Date',[Qualifying Sales $])
    VAR LastYearSales = SUMX('D01 DIM Date',[Qualifying Sales $ SameWeekLY])
    RETURN

    SUMX(FILTER(
    ALL( 'D01 DIM Date'),
    'D01 DIM Date'[Week#] = CurrentWeekNumber &&
    'D01 DIM Date'[Year] = CurrentYear
    ),[Qualifying Sales $])

     

    SalesLastYearSameWeek:=VAR CurrentWeekNumber = MAX( 'D01 DIM Date'[Week#] )
    VAR CurrentYear = MAX( 'D01 DIM Date'[Year] )
    VAR CurrentYearSales = SUMX('D01 DIM Date',[Qualifying Sales $])
    VAR LastYearSales = SUMX('D01 DIM Date',[Qualifying Sales $ SameWeekLY])
    RETURN

    SUMX(FILTER(
    ALL( 'D01 DIM Date'),
    'D01 DIM Date'[Week#] = CurrentWeekNumber &&
    'D01 DIM Date'[Year] = CurrentYear
    ),[Qualifying Sales $ SameDayLY])


    SSS% Store Level:=VAR CurrentYearSales = [SalesCurrentWeek]
    VAR LastYearSales = [SalesLastYearSameWeek]
    RETURN
    IF(
    NOT(ISBLANK(CurrentYearSales)) && NOT(ISBLANK(LastYearSales)),
    (CurrentYearSales - LastYearSales) / LastYearSales,
    BLANK()
    )

    SSS% Company Level:=VAR TotalCurrentYearSales = SUMX(ALL('F06 FACT Sales'[Store]), [SalesCurrentWeek])
    VAR TotalLastYearSales = SUMX(ALL('F06 FACT Sales'[Store]), [SalesLastYearSameWeek])
    RETURN
    IF(
    NOT(ISBLANK(TotalCurrentYearSales)) && NOT(ISBLANK(TotalLastYearSales)),
    (TotalCurrentYearSales - TotalLastYearSales) / TotalLastYearSales,
    BLANK()
    )