Previous Week Dates using Power Query M in Power Bi

Question

I'm using code from Chris Webb: Source

and here is the sample code from his site:

Ranges = {
            {"Today", 
            TodaysDate, 
            TodaysDate, 
            1},

            {"Current Week To Date", 
            Date.From(Date.StartOfWeek(TodaysDate)), 
            TodaysDate, 
            2},

            {"Current Month To Date", 
            Date.From(Date.StartOfMonth(TodaysDate)), 
            TodaysDate, 
            3},
            {"Current Year To Date", 
            Date.From(Date.StartOfYear(TodaysDate)), 
            TodaysDate, 
            4},

            .......

I'm looking to get the previous week dates. I tried:

{"Previous Week", 

dates.AddWeeks(Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)),-1), 
            TodaysDate, 
    4},

Which works but of course it also adds the dates for the current week (which is Thursday to Thursday in my case). Any ideas about only getting the previous week based on this method? Possibly subtracting the dates from the current week and previous week?

I'm not trying to do week flags or use DAX or R. Preferably in this format.

Anything would help!!! Thanks!

C.Mayers C.Mayers · Accepted Answer · 2019-03-04T18:32:30

I figured it out. Here is my final code if anyone is looking for something like this.

It includes Yesterday and Previous Week with a custom week start date. I'm new at this so I guess that's my excuse for it being right in front of me and me missing it!

= {
            {"Today", 
            TodaysDate, 
            TodaysDate, 
            1},

            {"Yesterday", 
            Date.AddDays(TodaysDate,-1), 
            Date.AddDays(TodaysDate,-1), 
            2},

            {"Week to Date", 
            Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)), 
            TodaysDate, 
            3},

            {"Previous Week", 
            Date.AddWeeks( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)),-1), 
            Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)),
            4},

            {"Current Month To Date", 
            Date.From(Date.StartOfMonth(TodaysDate)), 
            TodaysDate, 
            5},

            {"Current Year To Date", 
            Date.From(Date.StartOfYear(TodaysDate)), 
            TodaysDate, 
            6},

            {"Rolling 13Weeks", 
            Date.AddWeeks(TodaysDate,-13) + #duration(1,0,0,0), 
            TodaysDate, 
            7}
    }

Previous Week Dates using Power Query M in Power Bi

1 Answers