Measure for Same Store Sales Growth

[Lasangi]

Hi,

Need some help with the below scenario:

I have a sales table with daily sales for different stores. Need to calculate same store sales growth at store level and company level in comparison to previous year. Also have the date table linked to sales table

I have the below measures working-

 Sales $ = sum('F06 FACT Sales'[Value])

 Sales LY =CALCULATE( [Sales $] ,DATEADD('D01 DIM Date'[Date],-364,DAY))

Please see below example: SSS% for this store should be 0.2%, ignoring the 29/9/2022 as there was no sames for comparable period this year. Hopefully this makes sense!

Store	2022-09-27	2022-09-28	2022-09-29	2022-09-30	2022-10-01	2022-10-02	2022-10-03
AAA	8,027	8,700	9,883	12,005	11,131	9,019	8,572	57,453
Store	2023-09-26	2023-09-27	2023-09-28	2023-09-29	2023-09-30	2023-10-01	2023-10-02
AAA	9,442	9,607		6,971	11,423	10,723	9,429	57,594
								141
								0.2%

[AndreDeLange]

The SAMEPERIODLASTYEAR function in DAX is designed to return a set of dates in the previous year for the same period as the specified dates. It's commonly used in scenarios where you want to compare measures, like sales, against the same period in the prior year.

However, its default behavior is based on the calendar hierarchy: year, quarter, month, and day. It does not inherently respect weeks. So, if you provide it with a set of dates that represent a specific week in the current year, it will return the exact same dates for the previous year rather than the corresponding "week number."

For example, if you're looking at sales for the week starting June 1st, 2023 (Thursday) and ending June 7th, 2023 (Wednesday), using SAMEPERIODLASTYEAR would return dates for June 1st, 2022 (Wednesday) to June 7th, 2022 (Tuesday). You can see the mismatch in days of the week.

If you need to compare sales for the exact "week number" of the previous year, you'd need a more customized approach. This often involves having a well-structured date table with week numbers and using it in conjunction with DAX functions to get the desired results.

Assuming you have a date table (Calendar) with columns Date, Year, and WeekNumber, you can achieve your goal with something like:

SalesLastYearSameWeek =
VAR CurrentWeekNumber = MAX( Calendar[WeekNumber] )
VAR CurrentYear = MAX( Calendar[Year] )
RETURN
CALCULATE(
[TotalSales],
FILTER(
ALL( Calendar ),
Calendar[WeekNumber] = CurrentWeekNumber && Calendar[Year] = CurrentYear - 1
)
)

[AndreDeLange]

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*

[littlemojopuppy]

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!

[littlemojopuppy]

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.

[AndreDeLange]

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])

    )

RETURN

CALCULATE(

    SUM(FactTable[Sales]),

    DATESYTD('Date'[Date]),

    INTERSECT(

        StoresOpenThisPeriod,

        StoresOpenLastPeriod

    )

)

and 

Relevant 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])

    )

RETURN

CALCULATE(

    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 seeing

Same 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?

[littlemojopuppy]

Look at doing a similar thing with INTERSECT for dates.  Same store sales means stores open at the same time along with dates open.

[Lasangi]

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 🙂

[Lasangi]

Hi @AndreDeLange ,

Is this complication something you can help with? Thank you

[AndreDeLange]

So looking at your data, you're saying that the sales for 29/09/2022 should be excluded because you had no sales for the 29/09/2023 (one year later, same day of month, same month).

What about the sales for the 26/09/2023, when you had no sales in your table for the 26/09/2022? Does the sales value need to be excluded for 26/09/2023 as well?

[AndreDeLange]

The same question for the 3/10/2023. No sales, so why are you including 3/10/2022?

[Lasangi]

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

[Lasangi]

in this case, we are looking at the particular week (eg. week 40) so not comparing the exact date, but the day (Monday sales need to be compared with Monday last year for example)

in this scenario - we only need to exclude the Thursday sales (2023/9/28 and corresponding Thursday last year is 29/9/2022)

[AndreDeLange]

Ok so if your week starts on a Tuesday, then you're comparing week 40 for both 2022 and 2023.

You have sales for Tue, Wed, Thu, Sat, Sun and Mon on both week 40's for 2022 and 2023, but not Friday.

Looking at this in Vanilla Excel it seems to me that it's really the Friday value that you want to exclude, not the Thursday?

Am I misunderstanding?

[Lasangi]

6971 is sales for 29/9/2023 missing sales for 28/9/2023

[AndreDeLange]

Aha! Couldn't really tell from how your data pasted. I think I have a solution now...

[AndreDeLange · Accepted solution]

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()
)

[Lasangi]

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?

[AndreDeLange]

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"

[Lasangi]

Hi @AndreDeLange ,

Worked like magic! Thank you so much for helping with my query! 

[AndreDeLange]

Super, glad I could help 😁