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()
)
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*
- Lasangi2 years agoFrequent Visitor
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 🙂
- Lasangi2 years agoFrequent Visitor
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 🙂
- Lasangi2 years agoFrequent Visitor
Hi AndreDeLange ,
Is this complication something you can help with? Thank you- AndreDeLange2 years agoHelper II
Still trying a few things 🙂
- littlemojopuppy2 years agoCommunity Champion
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!
- littlemojopuppy2 years agoCommunity Champion
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.
- AndreDeLange2 years agoHelper II
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:
Relevant Sales YTD =VAR StoresOpenThisPeriod =CALCULATETABLE(VALUES(FactTable[StoreID]),DATESBETWEEN('Date'[Date],STARTOFYEAR('Date'[Date]),ENDOFYEAR('Date'[Date])))VAR StoresOpenLastPeriod =CALCULATETABLE(VALUES(FactTable[StoreID]),PREVIOUSYEAR('Date'[Date]))RETURNCALCULATE(SUM(FactTable[Sales]),DATESYTD('Date'[Date]),INTERSECT(StoresOpenThisPeriod,StoresOpenLastPeriod))
andRelevant Sales LYTD =VAR StoresOpenThisPeriod =CALCULATETABLE(VALUES(FactTable[StoreID]),DATESBETWEEN('Date'[Date],STARTOFYEAR('Date'[Date]),ENDOFYEAR('Date'[Date])))VAR StoresOpenLastPeriod =CALCULATETABLE(VALUES(FactTable[StoreID]),PREVIOUSYEAR('Date'[Date]))RETURNCALCULATE(SUM(FactTable[Sales]),DATEADD(DATESYTD('Date'[Date]), -1, YEAR),INTERSECT(StoresOpenThisPeriod,StoresOpenLastPeriod))
and added a few new measures:Current Sales = SUM(FactTable[Sales])LY Sales = CALCULATE([Curernt Sales],SAMEPERIODLASTYEAR('Date'[Date])) //this is just for illustration purposes of the issue I'm seeingSame Store Growth % = DIVIDE([Sales YTD]-[Sales LYTD],[Sales LYTD])This is the fact table:
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):Mine: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?