Forum Discussion
Measure for Same Store Sales Growth
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()
)
Hello it's me again!
Happy new year!
New year - new problem to solve 🙂
So this approach worked fine until i reached the week ending 1/1/24
corresponding dates:
| Current | Last year | |
| 2023-12-26 | 2022-12-27 | |
| 2023-12-27 | 2022-12-28 | |
| 2023-12-28 | 2022-12-29 | |
| 2023-12-29 | 2022-12-30 | |
| 2023-12-30 | 2022-12-31 | |
| 2023-12-31 | 2023-01-01 | !!! |
| 2024-01-01 | 2023-01-02 |
It comes from this measure ( i think), because the corresponding dates are in the same year.
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
)
)
Can you please help to fix this?
Hello again!
So with your 2023/12/31 date, it seems the problem is with how the week of the year is being assigned. For your table above to hold true, the week number for the 1st and 2nd of Jan 2023 would have to be the same as the week number as for 2023/12/27 through to 2023/12/31.
This means that we can't use the week number assigned by PQ as I showed before, and we don't want it to reset with the start of every calendar year. Instead, we can calculate a "YearWeekIndex" column, which calculates a unique number for each week starting on a Tuesday, from the beginning of our calendar.
I have pasted the M-code I used for the calendar, which generates a calendar table from scratch (credit to Matt Allington for the original code, which I modified for my own purposes).
It's the steps after the step named "Reference4" that are relevant to this problem. What I'm doing here is to assign a unique index number for each week, which increments through the whole calendar.
I then expand the days that are part of that WeekIndex, and merge that back to the original calendar to bring back the index number into the calendar table.
Once you have the new column, the measures can be modified as follows:
SalesLastYearSameWeek#=CALCULATE([Sales $],FILTER(ALL('D01 DIM Date'),'D01 DIM Date'[Day of Week]=MAX('D01 DIM Date'[Day of Week])&&'D01 DIM Date'[YearWeekIndex]=MAX('D01 DIM Date'[YearWeekIndex])-52))
SalesCurrentWeek=VAR CurrentWeekNumber = MAX( 'D01 DIM Date'[YearWeekIndex] )
RETURN
SUMX(FILTER(
ALL( 'D01 DIM Date'),
'D01 DIM Date'[YearWeekIndex] = CurrentWeekNumber
),[Qualifying Sales $])
SalesLastYearSameWeek:=VAR CurrentWeekNumber = MAX( 'D01 DIM Date'[YearWeekIndex] )
RETURN
SUMX(FILTER(
ALL( 'D01 DIM Date'),
'D01 DIM Date'[YearWeekIndex] = CurrentWeekNumber
),[Qualifying Sales $ SameWeekLY])
The other measures remain the same.
Calendar M-code (paste into PQ Advanced Editor):
let
Source = List.Dates(Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))-4,1,1,0,0,0)), Duration.Days(Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))+10,1,1,0,0,0))-Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))-4,1,1,0,0,0))), #duration(1, 0, 0, 0)),
#"Converted to Table1" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month", each Date.MonthName([Date]), type text),
#"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month", each Text.Start(_, 3), type text}}),
#"Inserted Month" = Table.AddColumn(#"Extracted First Characters", "Month#", each Date.Month([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.StartOfWeek([Date],2), type date),
#"Inserted Week of Year1" = Table.AddColumn(#"Inserted Start of Week", "Week of Year", each Date.WeekOfYear([Date],2), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Week of Year1", "Yearmonth", each [Year]*100+[#"Month#"]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Yearmonth", Int64.Type}}),
Reference1 = Table.ReorderColumns(#"Changed Type1",{"Date", "Year", "Month", "Month#", "Yearmonth", "Day of Week", "Week of Year"}),
#"Removed Other Columns" = Table.SelectColumns(Reference1,{"Yearmonth"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "YMIndex", 0, 1),
#"Merged Queries" = Table.NestedJoin(Reference1,{"Yearmonth"},#"Added Index",{"Yearmonth"},"Added Index",JoinKind.LeftOuter),
#"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"YMIndex"}, {"YMIndex"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Added Index",{{"YMIndex", Int64.Type}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type2",{"Date", "Year", "Month", "Month#", "Yearmonth", "YMIndex", "Day of Week", "Week of Year"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns1", "Year", "Year - Copy"),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Year - Copy", type text}}, "en-AU"), "Year - Copy", Splitter.SplitTextByPositions({0, 2}, false), {"Year - Copy.1", "Year - Copy.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position",{{"Year - Copy.1", Int64.Type}, {"Year - Copy.2", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Year - Copy.2", "Shortyear"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [#"Month#"] <=6 then [#"Shortyear"] - 1 else [#"Shortyear"]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each if [#"Month#"] >=7 then [Shortyear] +1 else [Shortyear]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Shortyear"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.1", type text}, {"Custom", type text}, {"Year - Copy.1", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type4", "FinYear", each [#"Year - Copy.1"]&[Custom]&"/"&[Custom.1]),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"FinYear", type text}}),
Reference2 = Table.RemoveColumns(#"Changed Type5",{"Year - Copy.1", "Custom", "Custom.1"}),
#"Removed Other Columns1" = Table.SelectColumns(Reference2,{"FinYear"}),
#"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1"),
#"Added Index1" = Table.AddIndexColumn(#"Removed Duplicates1", "FYIndex", 0, 1),
#"Merged Queries1" = Table.NestedJoin(#"Reference2",{"FinYear"},#"Added Index1",{"FinYear"},"Added Index1",JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index1", {"FYIndex"}, {"FYIndex"}),
#"Inserted End of Week" = Table.AddColumn(#"Expanded Added Index1", "Previous Friday", each Date.AddDays(Date.EndOfWeek([Date]),-2-7), type date),
#"Duplicated Column1" = Table.DuplicateColumn(#"Inserted End of Week", "Month", "FinMonth"),
#"Added Custom4" = Table.AddColumn(#"Duplicated Column1", "FinMonth#", each if [#"Month#"] >= 7 then [#"Month#"] -6 else [#"Month#"] +6),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Custom4",{{"FinMonth#", Int64.Type}, {"Week of Year", Int64.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type6",{"Date", "Year", "Month", "Month#", "Yearmonth", "YMIndex", "Day of Week", "Week of Year", "FinYear", "FinMonth", "FinMonth#", "FYIndex", "Previous Friday"}),
#"Inserted Quarter" = Table.AddColumn(#"Reordered Columns", "YQtr", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Custom5" = Table.AddColumn(#"Inserted Quarter", "Custom", each [Year]*10+[YQtr]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"YQtr"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "YearQtr"}}),
Reference3 = Table.TransformColumnTypes(#"Renamed Columns2",{{"YearQtr", Int64.Type}}),
#"Removed Other Columns2" = Table.SelectColumns(Reference3,{"YearQtr"}),
#"Removed Duplicates2" = Table.Distinct(#"Removed Other Columns2"),
#"Added Index2" = Table.AddIndexColumn(#"Removed Duplicates2", "YQIndex", 0, 1),
#"Merged Queries2" = Table.NestedJoin(#"Reference3",{"YearQtr"},#"Added Index2",{"YearQtr"},"Reference3",JoinKind.LeftOuter),
#"Expanded Reference3" = Table.ExpandTableColumn(#"Merged Queries2", "Reference3", {"YQIndex"}, {"YQIndex"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Reference3",{{"Week of Year", "Week#"}}),
#"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Week#", Int64.Type}}),
Reference4 = Table.AddColumn(#"Changed Type7", "Day of Month", each Date.Day([Date]), Int64.Type),
#"Removed Other Columns4" = Table.SelectColumns(Reference4,{"Date", "Day of Week"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns4", each ([Day of Week] = 2)),
#"Added Index4" = Table.AddIndexColumn(#"Filtered Rows", "TuesWeekIndex", 1, 1, Int64.Type),
#"Removed Columns2" = Table.RemoveColumns(#"Added Index4",{"Day of Week"}),
#"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns2",{"Date", "TuesWeekIndex"}),
#"Added Custom6" = Table.AddColumn(#"Reordered Columns3", "WeekDates", each List.Dates([Date],7,#duration(1,0,0,0))),
#"Expanded WeekDates" = Table.ExpandListColumn(#"Added Custom6", "WeekDates"),
#"Removed Duplicates3" = Table.Distinct(#"Expanded WeekDates"),
#"Changed Type8" = Table.TransformColumnTypes(#"Removed Duplicates3",{{"WeekDates", type date}}),
#"Merged Queries3" = Table.NestedJoin(#"Reference4", {"Date"}, #"Changed Type8", {"WeekDates"}, "Added Index3", JoinKind.LeftOuter),
#"Expanded Added Index3" = Table.ExpandTableColumn(#"Merged Queries3", "Added Index3", {"TuesWeekIndex"}, {"TuesWeekIndex"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Added Index3",null,0,Replacer.ReplaceValue,{"TuesWeekIndex"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Date", Order.Ascending}}),
#"Renamed Columns4" = Table.RenameColumns(#"Sorted Rows",{{"TuesWeekIndex", "YearWeekIndex"}}),
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns4",{"YMIndex", "Week#", "FinYear", "FinMonth", "FinMonth#", "FYIndex", "Previous Friday", "YearQtr", "YQIndex"})
in
#"Removed Columns3"