Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Build calendar table with fixed weeknumbers assigned to months, quarters etc.

Hi all!   I need some help to figure out how I can a calender that follows the logic in the attached picture.    We have sales cycles per week, so running on a standard ISO calender does not work...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Seems that there are some issues in your if statement.

    Like "if [WeekNb] >= 6 or [WeekNb] <=9 then 2", the logic should be "and" not "or", this is why everything else was tagged with 2.

    Try to modify the formula like this:

    = Table.AddColumn(
#"Changed Type", "NT-MonthNb", each 
if [WeekNb] >= 53 and [MonthNb] = 1 or [WeekNb] <= 5 then 1 else 
if [WeekNb] >= 6 and [WeekNb] <= 9 then 2 else 
if [WeekNb] >= 10 and [WeekNb] <= 13 then 3 else 
if [WeekNb] >= 14 and [WeekNb] <= 18 then 4 else 
if [WeekNb] >= 19 and [WeekNb] <= 22 then 5 else 
if [WeekNb] >= 23 and [WeekNb] <= 26 then 6 else 
if [WeekNb] >= 27 and [WeekNb] <= 31 then 7 else
if [WeekNb] >= 32 and [WeekNb] <= 35 then 8 else  
if [WeekNb] >= 36 and [WeekNb] <= 39 then 9 else
if [WeekNb] >= 40 and [WeekNb] <= 44 then 10 else  
if [WeekNb] >= 45 and [WeekNb] <= 48 then 11 else
if [WeekNb] >= 49 and [WeekNb] <= 53 or [MonthNb] < 1 then 12 else null,
Int64.Type)

    Attached a sample file in the below, hopes it could help.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous , there are some out-of-the-box functions in this regard,

let
    Source = Table.FromList(List.Dates(#date(2021,1,1),365,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}),
    #"Added Calculation" = Table.AddColumn(Source, "Calculation", each [Month = Date.ToText([Date], "MMMM"), Week = Date.WeekOfYear([Date]), Quarter = Date.QuarterOfYear([Date]), Tertial = Number.RoundUp(Date.Month([Date])/4)]),
    #"Expanded Month" = Table.ExpandRecordColumn(#"Added Calculation", "Calculation", {"Month", "Week", "Quarter", "Tertial"}, {"Month", "Week", "Quarter", "Tertial"})
in
    #"Expanded Month"
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi CNENFRNL 

     

    This did not give quite the result I was hoping for. Let me put it in context. 

     

    I have a regular calendar table following the ISO standard. I need to add columns for company months, quarters and tertials based on the above structure. So week 5 on the ISO calendar is alway in January, even tho all the dates are in February. 

     

    I've added a picture file to show the setup. I have a dynamic date table, I wish to add these columns to it. And I do not know how to write that code. 

     

    Christer