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()
)
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()
)
- AndreDeLange2 years agoHelper II
Super, glad I could help 😁