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()
)
The same question for the 3/10/2023. No sales, so why are you including 3/10/2022?
Sorry my bad - sales week is Tues to Mon, so for the week 26/9/2023 to 2/10/2023 the corresponding week in 2022 is 27/9/2022 to 3/10/2022