Forum Discussion
Measure for Same Store Sales Growth
- 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 includeSalesCurrentWeek:=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])
RETURNSUMX(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])
RETURNSUMX(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()
)
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.
Thank you for walking through the alternative approach, littlemojopuppy . I was keen to test it since I have never used the INTERSECT function, but the measure for Relevant Sales LYTD is not giving the desired result. I had to make a few small tweaks to avoid the errors from pasting your DAX code:
and
and added a few new measures:
and the Fact table has a relationship to the 'Date' table:
Comparing the output of your Sales LYTD with my measure for the SalesLastYearSameWeek, mine is correctly excluding the value for the 28th of September 2022 (9,883), whereas yours is dropping the last sale of week 40 from the previous year (8,572):
Yours:
It seems your measure isn't "lining up" last year's sales into the correct week for comparison.
Let me know if I am missing something?
- littlemojopuppy2 years agoCommunity Champion
Look at doing a similar thing with INTERSECT for dates. Same store sales means stores open at the same time along with dates open.