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 comparison to previous year. Also have the date table linked to sales table

 

I have the below measures working-

 Sales $ = sum('F06 FACT Sales'[Value])
 Sales LY =CALCULATE( [Sales $] ,DATEADD('D01 DIM Date'[Date],-364,DAY))
 
Please see below example: SSS% for this store should be 0.2%, ignoring the 29/9/2022 as there was no sames for comparable period this year. Hopefully this makes sense!

 

Store2022-09-272022-09-282022-09-292022-09-302022-10-012022-10-022022-10-03 
AAA        8,027        8,700                     9,883     12,005     11,131        9,019        8,572     57,453
         
         
Store2023-09-262023-09-272023-09-282023-09-292023-09-302023-10-012023-10-02 
AAA        9,442        9,607         6,971     11,423     10,723        9,429     57,594
                   141
        0.2%



Need Help
  • 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()
    )

25 Replies

  • AndreDeLange's avatar
    AndreDeLange
    Icon for Helper II rankHelper II
    2 years ago

    I would suggest you use a different measure for getting Sales LY, so that it takes into account leap years. DAX has an inbuilt time intelligence function called SAMEPERIODLASTYEAR. This is how you would apply it:
    Sales LY = CALCULATE( [Sales $], SAMEPERIODLASTYEAR('D01 DIM Date'[Date]) )

    Just remember that inbuilt time intelligence functions only work with a contiguous Date column.

     

    For calculating the SSS% at store level, you wish to consider only those days for which sales are available for both current year and the last year:

    SSS% Store Level =
    VAR CurrentYearSales = [Sales $]
    VAR LastYearSales = [Sales LY]
    RETURN
    IF(
    NOT(ISBLANK(CurrentYearSales)) && NOT(ISBLANK(LastYearSales)),
    (CurrentYearSales - LastYearSales) / LastYearSales,
    BLANK()
    )

     

    To calculate the SSS% at the company level, you would aggregate all store level SSS% for which you have sales data for both current and previous year:

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

     

    Remember to format these measures as Percentage type.

     

    *Please remember to hit Like if this has solved your question*

    • Lasangi's avatar
      Lasangi
      Frequent Visitor
      2 years ago

      Hi AndreDeLange,

       

      Thank you for your quick response! much appreciated!

      The reason for using  Sales LY =CALCULATE( [Sales $] ,DATEADD('D01 DIM Date'[Date],-364,DAY)) was to stick to same week, when I presented weekly results.

      Does this affect the rest of your approach?

       

      thanks again 🙂

    • Lasangi's avatar
      Lasangi
      Frequent Visitor
      2 years ago

      Hi AndreDeLange ,

       

      The measure doesn't work in the case of my example above, instead of 0.2% growth, it gives -0.14%. I need the measure to exclude the sales for the day there were no sales, but results are for the week if that makes sense 🙂

    • littlemojopuppy's avatar
      littlemojopuppy
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Hi AndreDeLange 

       

      Part of the same store sales growth formula is calculating the change from previous day/month/year sales to current day/month/year.  But the other part of the formula is that it should only include stores that were open and operating (meaning not closed due to blizzard, fire, hurricane or holiday) in both those time periods.

       

      What I read seems to be on track for the difference from period to period, but in order to get the stores open in both periods the easiest way to accomplish that is using the INTERSECT function.  Something like this...

      INTERSECT(
	StoresOpenLastYear,
	StoresOpenThisYear
)

      You can create variables for last year and this year like this...

      CALCULATETABLE(
	VALUES(FactTable[StoreID]),
	DATESBETWEEN(
		Date[Date],
		STARTOFYEAR(Date[Date]),
		ENDOFYEAR(Date[Date])
	)
)

      This is obviously for this year...adjust accordingly for last year, or if you want month, etc.  There's a bunch of time intelligence functions that can help.

       

      Hope this helps!

      • littlemojopuppy's avatar
        littlemojopuppy
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Here is a much easier method to solve this...

         

        Three measures.  One for (hypothetically) this year sales to date:

        VAR	StoresOpenThisPeriod =
	CALCULATETABLE(
		VALUES(FactTable[StoreID]),
		DATESBETWEEN(
			Date[Date],
			STARTOFYEAR(Date[Date]),
			ENDOFYEAR(Date[Date])
		)
	)
VAR	StoresOpenLastPeriod =
	CALCULATETABLE(
		VALUES(FactTable[StoreID]),
		PREVIOUSYEAR(Date[Date])
	)
RETURN

CALCULATE(
	SUM(FactTable[Sales],
	DATESYTD(Date[Date]),
	INTERSECT(
		StoresOpenThisYear,
		StoresOpenLastYear
	)
)

         

        One for last year sales to date

        VAR	StoresOpenThisPeriod =
	CALCULATETABLE(
		VALUES(FactTable[StoreID]),
		DATESBETWEEN(
			Date[Date],
			STARTOFYEAR(Date[Date]),
			ENDOFYEAR(Date[Date])
		)
	)
VAR	StoresOpenLastPeriod =
	CALCULATETABLE(
		VALUES(FactTable[StoreID]),
		PREVIOUSYEAR(Date[Date])
	)
RETURN

CALCULATE(
	SUM(FactTable[Sales],
	DATEADD(DATESYTD(Date[Date]), -1, YEAR),
	INTERSECT(
		StoresOpenThisYear,
		StoresOpenLastYear
	)
)

         

        Same store Growth = Measure #1 above minus Measure #2 above.  The pattern can easily be adapted to fit quarter, month, day or even week.  Hopefully you find this much easier to implement.

  • AndreDeLange's avatar
    AndreDeLange
    Icon for Helper II rankHelper II
    2 years ago

    The SAMEPERIODLASTYEAR function in DAX is designed to return a set of dates in the previous year for the same period as the specified dates. It's commonly used in scenarios where you want to compare measures, like sales, against the same period in the prior year.

    However, its default behavior is based on the calendar hierarchy: year, quarter, month, and day. It does not inherently respect weeks. So, if you provide it with a set of dates that represent a specific week in the current year, it will return the exact same dates for the previous year rather than the corresponding "week number."

    For example, if you're looking at sales for the week starting June 1st, 2023 (Thursday) and ending June 7th, 2023 (Wednesday), using SAMEPERIODLASTYEAR would return dates for June 1st, 2022 (Wednesday) to June 7th, 2022 (Tuesday). You can see the mismatch in days of the week.

     

    If you need to compare sales for the exact "week number" of the previous year, you'd need a more customized approach. This often involves having a well-structured date table with week numbers and using it in conjunction with DAX functions to get the desired results.

    Assuming you have a date table (Calendar) with columns Date, Year, and WeekNumber, you can achieve your goal with something like:

     

    SalesLastYearSameWeek =
    VAR CurrentWeekNumber = MAX( Calendar[WeekNumber] )
    VAR CurrentYear = MAX( Calendar[Year] )
    RETURN
    CALCULATE(
    [TotalSales],
    FILTER(
    ALL( Calendar ),
    Calendar[WeekNumber] = CurrentWeekNumber && Calendar[Year] = CurrentYear - 1
    )
    )