How to add last week X using Power Query

Power Query
1

Hi, In Power Query, I have a calendar table, how I can add the column called “LastWeekX” calculated based on the date? For example, if the date is today in the table, the LastWeekX=0. If the date is in last week, then LastWeekX=1, and so on. If the date is today two years ago, then the LastWeekX will continue to add on without reset when a new year starts.

Thanks for your help!

Mike

2

Hi @Mike,

Give this a go. Just copy the code in a new blank query.

let
    Today = Date.From( DateTimeZone.FixedUtcNow ()),
    StartDate = Date.StartOfMonth( Date.AddMonths( Today, -18)),
    EndDate = Date.EndOfMonth( Date.AddMonths( Today, 1 )),
    DateRange = List.Dates( StartDate, Number.From( EndDate-StartDate )+1, Duration.From(1)),
    DateTable = Table.RenameColumns( Table.TransformColumnTypes( Table.FromColumns( {DateRange} ), {"Column1", type date}), {"Column1", "Date"} ),
    StartOfWeekDayNum = 1,
    WeekOffset = Table.AddColumn(DateTable, "Week Offset", each 
        if  Date.StartOfWeek( [Date], StartOfWeekDayNum ) <= Date.StartOfWeek( Today, StartOfWeekDayNum ) then
            (Number.From( Date.StartOfWeek(Today, StartOfWeekDayNum) - Date.StartOfWeek([Date], StartOfWeekDayNum)))/7 else
            null
        , type number)
in
    WeekOffset

.
I hope this is helpful

2 Likes
3

Hi, @Melissa, that works, thanks a lot for your help!

Mike