# Week on Week Comparison - Time Intelligence

Hi ALL,
I am in a scenario, wherein I need to do week on week comparison for each month current week vs previous week, i have used the time intelligence week and previous week calculation showcased in the forum, but for me currently the challenge is the offset value works on a logic of 7 days irrespective of that date falling in next month as well. Example if you take Jan 2020 the last week of that month ends on 31st with weeknum = 5 but as we have used weeknum which starts on sunday it considers 1st feb as well in 5th week of Jan and my calculations doesnt work correctly.

My requirement is to always calculate current week with previous week and not on offset values i.e. as per the example above i should compare 1st week of feb ( i.e 1st Feb value ) with last week of Jan (26th till 31st ) and similarly comparing each week (starting sun)

Kindly request you to help me with this.

@Melissa - I know you are DAX champion and very well with Data offsets. Kindly request you to guide me on this

Week on week.pbix (501.1 KB)

Hi @Vishy,

So from what I understand the actual problem is applying the correct Weeknumber logic according to your business requirement in the Date table, right? Because I see no issue with the values displayedâŚ

If so, can you set up a full Date table in Excel with just 2 columns [Date] and [Weekno] - so I can help you sort that out. Thanks!

Hi @Melissa

Kindly request you find attached the Excel with full date and week of month column created using power query Weekof month function and Weeknum column created using dax. As per the screen shot attached , what i need is week num column should step wise increase as per the week of the month value i.e. as said earlier if you look at the cells highlighted in yellow the week 5 for Jan 2020 ends at 31st Jan 2020. And at the same time new 1st week for Feb starts at 1st Feb and then the 2nd week from 2nd feb (starting Sun) . But now if you look at the weeknum function it extends the week 5 of Jan month till 1st Feb, where as in my case it should show 6 as the number i.e. 1st Week of Feb and 7 for 2nd week of feb. This would help me to get values as per my business scenario in the previous week calculation

excel has been also attached.
DateTest.xlsx (22.7 KB) Week on week.pbix (509.0 KB)

Have attached the Pbix file as well with Dates 2 table being created , it has the new weekof month column created as explained in above section .

Think I was exploring the same angle

Iâve added a [YMW number] and a [Custom week] number to your Dates table with this logic.

``````InsertWeekOfMonth = Table.AddColumn(InsertWeekNumber, "Week Number2", each Date.WeekOfMonth([Date], Day.Sunday)),
InsertWkMnthNum = Table.AddColumn(InsertWeekOfMonth, "YMW number", each [Year] * 10000 + [MonthOfYear] * 100 + [Week Number2], type number),
BufferTable = Table.Buffer(Table.Distinct( InsertWkMnthNum[[Year], [YMW number]])),
InsertCustomWeek = Table.AddColumn(InsertWkMnthNum, "Custom Week no", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[YMW number] <= OT[YMW number] and IT[Year] = OT[Year]))),
``````

then I created this measure:

``````Sales Previous Week v2 =
var vCurrYMW = SELECTEDVALUE( Dates[YMW number] )
var vMaxYMW = CALCULATE(MAX(Dates[YMW number]), FILTER( ALL(Dates), Dates[YMW number] < vCurrYMW ))
RETURN

SUMX(
FILTER( ALL( Dates ),
Dates[YMW number] = vMaxYMW ),
[Total Sales] )
``````

.
With this result

I hope this was helpful. Hereâs my sample file.
Week on week.pbix (523.6 KB)

Hi @Melissa

In the attachment provided by you I donât see the custom week calculation. It would be great if you share the latest pbix and just elaborate on the query of buffer table and custom week creation.

Thanks alot for this help

I checked it, this is the correct file - the calculation for the Custom week is in the PQ Date table function.
Week on week.pbix (523.6 KB)

@Melissa - thanks for the pbix , can just help me with how does the buffer and custom week power query works please

Sure.

You can also visualize the nested table that is created in your Dates table, by omitting the Table.RowCount in the InsertCustomWeek step - when you click in the white space, you can see a preview of the nested table. The number of rows in that table change with each week. And so we can use it to create a custom week number for each date.
.

``````let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),

// Add a Week of Month column, where the week starts on Sunday
InsertWeekOfMonth = Table.AddColumn(InsertWeekNumber, "Week Number2", each Date.WeekOfMonth([Date], Day.Sunday)),
// Create a Unique Interger for each Week
InsertWkMnthNum = Table.AddColumn(InsertWeekOfMonth, "YMW number", each [Year] * 10000 + [MonthOfYear] * 100 + [Week Number2], type number),
// Load the disinct combination of the columns [Year] and [YMW number] into memory
BufferTable = Table.Buffer(Table.Distinct( InsertWkMnthNum[[Year], [YMW number]])),
// Count the rows in the Filtered BufferTable where [Year] = [Year] and [YMW number] <= [YMW number] on the current Record
InsertCustomWeek = Table.AddColumn(InsertWkMnthNum, "Custom Week no", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[YMW number] <= OT[YMW number] and IT[Year] = OT[Year]))),

InsertMonthnYear = Table.AddColumn(InsertCustomWeek,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
in
in
fnDateTable``````

@Melissa -
The actually when i tried to put the above query in my Date table (which comes from database and is not invoked function). The query returns a function in the Insert Customweek step, where as i believe it should have returned anew added column of Custom week no.

Kindly suggest or guide me with modification in that M query to get the new added column as in my case i am fetching the table directly from database(import mode)

Screen shot attached -

Hi @Vishy,

Can you show all the M code from the Advanced Editor, like the way I shared the calendar function code in post #8?

Thanks

@Melissa

Here you go

let
Source = Sql.Database(âdummyâ, âdummytestâ, [Query="/****** Script for SelectTopNRows command from SSMS ******/#(lf)SELECT [DateKey]#(lf) ,[FullDate]#(lf) ,[FullDate2]#(lf) ,[DayOfWeek]#(lf) ,[DayNameOfWeek]#(lf) ,[DayOfMonth]#(lf) ,[DayOfYear]#(lf) ,[WeekdayWeekend]#(lf) ,[PublicHoliday]#(lf) ,[WeekOfYear]#(lf) ,[MonthName]#(lf) ,[Short Month Name]#(lf) ,[Short Weekday Name]#(lf) ,[MonthOfYear]#(lf) ,[IsLastDayOfMonth]#(lf) ,[CalendarQuarter]#(lf) ,[CalendarYear]#(lf) ,[CalendarYearMonth]#(lf) ,[CalendarYearQtr]#(lf) ,[FiscalMonthOfYear]#(lf) ,[FiscalQuarter]#(lf) ,[FiscalYear]#(lf) ,[FiscalYearMonth]#(lf) ,[FiscalYearQtr]#(lf) ,[WeekOfMonth]#(lf) FROM [Connect_EDW].[dbo].[Dim_Date]#(lf) where CalendarYearMonth >= 201906 #(lf)and [FullDate2] < cast(getdate() as date)"]),
#âAdded Conditional Columnâ = Table.AddColumn(Source, âNew Week Of Monthâ, each if [WeekOfMonth] = 1 then âWeek 1â else if [WeekOfMonth] = 2 then âWeek 2â else if [WeekOfMonth] = 3 then âWeek 3â else if [WeekOfMonth] = 4 then âWeek 4â else âWeek 5â),
#âChanged Typeâ = Table.TransformColumnTypes(#âAdded Conditional Columnâ,{{âDayOfWeekâ, Int64.Type}, {âDayOfMonthâ, Int64.Type}, {âDayOfYearâ, Int64.Type}, {âWeekOfYearâ, Int64.Type}, {âMonthOfYearâ, Int64.Type}, {âCalendarQuarterâ, Int64.Type}, {âFiscalMonthOfYearâ, Int64.Type}, {âFiscalQuarterâ, Int64.Type}}),
#âMerged Columnsâ = Table.CombineColumns(Table.TransformColumnTypes(#âChanged Typeâ, {{âCalendarYearâ, type text}}, âen-GBâ),{âShort Month Nameâ, âCalendarYearâ},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),âMergedâ),
#âFiltered Rowsâ = Table.SelectRows(#âMerged Columnsâ, each true),
#âDuplicated Columnâ = Table.DuplicateColumn(#âFiltered Rowsâ, âCalendarYearMonthâ, âCalendarYearMonth - Copyâ),
#âSplit Column by Positionâ = Table.SplitColumn(Table.TransformColumnTypes(#âDuplicated Columnâ, {{âCalendarYearMonth - Copyâ, type text}}, âen-ZAâ), âCalendarYearMonth - Copyâ, Splitter.SplitTextByRepeatedLengths(4), {âCalendarYearMonth - Copy.1â, âCalendarYearMonth - Copy.2â}),
#âChanged Type1â = Table.TransformColumnTypes(#âSplit Column by Positionâ,{{âCalendarYearMonth - Copy.1â, Int64.Type}, {âCalendarYearMonth - Copy.2â, Int64.Type}}),
#âRenamed Columnsâ = Table.RenameColumns(#âChanged Type1â,{{âCalendarYearMonth - Copy.1â, âCalendarYearâ}}),
#âRemoved Columnsâ = Table.RemoveColumns(#âRenamed Columnsâ,{âCalendarYearMonth - Copy.2â}),
#âDuplicated Column1â = Table.DuplicateColumn(#âRemoved Columnsâ, âCalendarYearMonthâ, âCalendarYearMonth - Copyâ),
#âRenamed Columns1â = Table.RenameColumns(#âDuplicated Column1â,{{âCalendarYearMonth - Copyâ, âCalendarYearMonth - TXTâ}}),
#âChanged Type2â = Table.TransformColumnTypes(#âRenamed Columns1â,{{âCalendarYearMonth - TXTâ, type text}}),
#âDuplicated Column2â = Table.DuplicateColumn(#âChanged Type2â, âWeekOfYearâ, âWeekOfYear - Copyâ),
#âRenamed Columns2â = Table.RenameColumns(#âDuplicated Column2â,{{âWeekOfYear - Copyâ, âWeekOfYear - TXTâ}}),
#âChanged Type3â = Table.TransformColumnTypes(#âRenamed Columns2â,{{âWeekOfYear - TXTâ, type text}}),
#âDuplicated Column3â = Table.DuplicateColumn(#âChanged Type3â, âWeekOfMonthâ, âWeekOfMonth - Copyâ),
#âRenamed Columns3â = Table.RenameColumns(#âDuplicated Column3â,{{âWeekOfMonth - Copyâ, âWeekOfMonth - TXTâ}}),
#âChanged Type4â = Table.TransformColumnTypes(#âRenamed Columns3â,{{âWeekOfMonth - TXTâ, type text}}),
#âAdded Customâ = Table.AddColumn(#âChanged Type4â, âDateOrderâ, each [#âCalendarYearMonth - TXTâ]&[#âWeekOfYear - TXTâ]&[#âWeekOfMonth - TXTâ]),
#âChanged Type5â = Table.TransformColumnTypes(#âAdded Customâ,{{âDateOrderâ, Int64.Type}}),
#âAdded Custom1â = Table.AddColumn(#âChanged Type5â, âWeek of Month Sunday Startâ, each Date.WeekOfMonth([FullDate2])),
#âInsertWkMnthNumâ = Table.AddColumn(InsertWeekOfMonth, âYMW numberâ, each [CalendarYear] * 10000 + [MonthOfYear] * 100 + [Week Number2]),
#âBufferTableâ = Table.Buffer(Table.Distinct( InsertWkMnthNum[[CalendarYear], [YMW number]])),
// Count the rows in the Filtered BufferTable where [Year] = [Year] and [YMW number] <= [YMW number] on the current Record
InsertCustomWeek = Table.AddColumn(InsertWkMnthNum, âCustom Week noâ, each (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[YMW number] <= OT[YMW number] and IT[CalendarYear] = OT[CalendarYear])))
in
InsertCustomWeek

It was starting me in the face but anywayâŚ you need to remove the each keyword here.

1 Like

@Melissa - thanks for extended help . Can u suggest some good book or you tube channel which would quickly help me to ramp up on those buffer table and and how to use it kind of functions. Or overall power query as a whole.

I asked @Melissa that same question a while back.

Here was her response:

• Brian

@Melissa - we should probably place a version of your resource list in the M Showcase.

1 Like

@BrianJ Sounds like a great plan to me
.

Thanks guys this is very helpful