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…

image

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 :wink:

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
image

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.

I’ve added comments in your Calendar function below, to explain what each step does.

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),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
    AddFY
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])),
#“InsertWeekOfMonth” = Table.AddColumn(#“Added Custom1”, “Week Number2”, each Date.WeekOfMonth([FullDate2], Day.Sunday)),
#“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

Thanks for the quick reply!

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.

@Vishy,

I asked @Melissa that same question a while back. :grinning:

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 :wink:
.

Thanks guys this is very helpful