Forum Discussion

IF's avatar
IF
Post Prodigy
5 years ago
Solved

dynamic week table

Hi, I want to develop a dynamic week table, which should list the weeks for last 119 weeks (or last three years). The table will always keep the last 119 weeks. I plan to use the following, but the ...
Need Help
  • ziying35's avatar
    ziying35
    5 years ago

    IF 

    See if the following is what you're trying to achieve?

    //output
let
    Source = List.Generate(
                ()=> {Date.StartOfWeek(Date.AddWeeks(DateTime.LocalNow(), -119)), 1}, 
                each _{0} < Date.StartOfWeek(Date.AddWeeks(DateTime.LocalNow(), -1)), 
                each {Date.AddDays(_{0}, 7), _{1}+1}, 
                each {Text.Format("#{1}.#{0}", {Date.Year(_{0}), Date.WeekOfYear(_{0})}), _{1}}
            ),
    Tbl = Table.FromRows(Source, type table[#"Week No.Year" = text, Index = number])
in
    Tbl
edhans's avatar
edhans
Community Champion
5 years ago

Try this method IF 

let
    Source = {Number.From(Date.AddWeeks(DateTime.Date(DateTime.LocalNow()), -119))..Number.From(Date.AddWeeks(DateTime.Date(DateTime.LocalNow()), -1))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}})
in
    #"Changed Type"

I use this to generate date tables going back decades and it works well. I just did one yesterday that went to Jan 1, 1999 and the 8K rows of the date table with about 30 columns ultimately loaded in a few seconds, so 118 weeks is super fast.

IF's avatar
IF
Post Prodigy
5 years ago

How can I show the week values such as:

06.2019

07.2019

08.2019

09.2019

10.2019

11.2019

12.2019

13.2019