Conditional Date table

[Anonymous]

I have created a date table but now need to add “month” column and “week range” column;  but with a condition:

• If days of a month ends on Tuesday e.g. March 2020 (29th, 30th and 31st March 2020), it should be considered as April month. Reversing, if a month starts on Thursday or after it should be considered a part of previous month, e.g. 1st and 2nd May should a considered as last week of April
• Format for week column and example of conditions in the date table:

[dax]

Hi @Anonymous , 

I am still  confused for your logic, You could refer to my sample for details(column2 or column 4). I will try to understand your logic , then  modify it later.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for sharing the PBIX, I can totally understand that this is very confusing. Thanks for undersatnding.

I believe Col 2 and 4 gives the month to which the date should belong? Col 4 is very close to what I am trying to arrive at. Basically, the "Wednesday" of the last and first week of the month (respectively) is the cut-off for deciding which month it should fall-in, as I tried to put earlier with the calendar. If the end of the month has maximum number of days  of its end week less than Tuesday (weekends included) , the last days of the current month is considered under next month.

For instance:

Eg1: Aug 30 & 31, 2020, week#36, ends in a week before wednesday, it should be considered under september month even though its August. 

E.g. 2 : November 29 & 30, 2020, are the last week of November but ends before wednesday, hence they will be a part of december.

E.g. 3 : January 1& January2 , 2021 will be a part of december and also a part of 2020 rather than 2021.

 Please see the calendar screen below:

Coming back to your file. Col 4 gives the closest result except for following days:

1. 31st May should be 6 (June) instead of 5
2. 30th and 31st Aug should be 09 instead of 08
3. These should be under 10 (October) instead of 9 (September )

   27-Sep

   28-Sep

   29-Sep

   30-Sep

   1-Oct

   2-Oct

   3-Oct

4. 29 Nov and 30th Nov Should be 12 (December) instead of 11
5. 1 Jan 2021 and 2 nd jan 2021 should be December and under year 2020

Please let me know, if you need any further expalnation. And thanks again for help.

Thanks, Shilpi

[dax · Accepted solution]

Hi @Anonymous ， 

You could try to refer to below M code to see whteher it work or not.

let
    Source = List.Dates(#date(2020,1,1),365,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "WeekNumOfYear", each Date.WeekOfYear([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WeekName", each Date.WeekOfMonth([Column1])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month", each Date.Month([Column1])),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Month", "WeekNumOfYear", "WeekName"}, {{"Count", each Table.RowCount(_), type number}, {"all", each _, type table [Column1=date, WeekNumOfYear=number, WeekName=number, Month=number]}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"WeekNumOfYear"}, {{"maxc", each List.Max([Count]), type number}, {"allm", each _, type table [Month=number, WeekNumOfYear=number, WeekName=number, Count=number, all=table]}}),
    #"Expanded allm" = Table.ExpandTableColumn(#"Grouped Rows1", "allm", {"Month", "Count"}, {"Month", "Count"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded allm", "Custom", each if [maxc]=[Count] then [Month] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each [Custom] <> null and [Custom] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"maxc", "Month", "Count"}),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom2", {"WeekNumOfYear"}, #"Removed Columns", {"WeekNumOfYear"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Custom"}, {"Custom"})
in
    #"Expanded Removed Columns1"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Hi

I get the requirement of full weeks, but don't understand the logic of the weekly-ranges:

Shouldn't weeks also cover Monday-Sunday-intervals?

[MattAllington]

My advice is this

Create a list of dates in Excel

Write a formula in Excel that does what you need

then do it as a conditional column in PQ