Custom calendar

[Mederic]

Hello everyone,
I would like to customise a calendar that has a certain number of columns.
I am unable to adapt the M code for the two columns ‘Weeks Nr.’ and ‘Range Days’.
The ultimate goal is to create a pivot table that looks like the one in the attached file.

If you have a better presentation for the pivot table, I would be interested.
Thank you in advance.

Best regards

[Mederic]

Good evening everyone,
@SundarRaj  Thank you for your solution, which I have just looked at. The proposal is interesting, but as mentioned by jaineshp , I am starting from Power query Editor and not from an existing table in Excel.

@jaineshp  Thank you for summarising the steps clearly.
That is indeed the approach to take. The same as pa4cal, but removing the "Weeks Nr" column, which is no longer needed.
I will therefore use his solution
@p45cal , that's perfect, thank you very much.

I appreciated the approach you suggested.
Here is the result with 3 full weeks + the last week up to the "end" date.

Thank you all.
Have a good evening.

Translated with DeepL.com (free version)

[p45cal]

I foresee a hiccup with the likes of #duration(26,0,0,0).

If you change the end date you will not necessarily get the full week that the pivot table implies.

Example: Change the end date to 15th Jan 2025 and you get this pivot:

where the data in the red rectangle implies a full week, but it's not:

 

The 18th and 19th Dec 2024 values are not included in the 30, it should be 36.

This is what the query looks like:

Perhaps use:

 if (x) < Date.AddWeeks(Date.StartOfWeek(end, Day.Wednesday),-4) then

 which will guarantee a full week's data is included

then the pivot becomes:

I note also that you wanted 4 weeks data at the end, not 5; changing -4 to -3 will put that right.

[Mederic]

Hello @p45cal ,

Thank you for pointing out and correcting the problem.
I did indeed have it in the back of my mind.
I just tested it with a few dates and it works with the full period from Sunday to Saturday.
I no longer have to worry about manually changing the previous condition.
Best regards

[p45cal · Accepted solution]

How about this as a pivot table presentation:

I stayed with Wednesday being the start of a week so that my results match your results.

Change your end date in cell 'end' to one within the first 2 or 3 weeks of the year to see what happens when you refresh the pivot at cell V13.

See the calendar query in the attached which outputs directly to the pivot.

Hope it gives you some ideas.

Edit: Note, that if you want the week to begin on a Monday, there are 2 places where you need to change that:

1. The first step called FirstDateOfWeekNos where you change Day.Wednesday to Day.Monday (or just delete Day.Wednesday and its preceding comma since Monday is the default if you don't supply that argument), and

2. The step called Inserted Start of Week where you do the same.

[Mederic]

@SundarRaj , did you post the correct file with your changes?
I don't see any difference with mine.

@tharunkumarRTK ,thank you very much, I took inspiration from your last two lines of code and integrated them into mine. It works well (see new file)
I think I'll adapt the number of weeks to ISO to start from a full week.
My only problem is that I have to manually modify this part "#duration(26,0,0,0)", so that the first of the last four weeks starts on a Monday.

I'm waiting to see the solution from SundarRaj to see which solution to go with.
Best regards

[SundarRaj]

Hi @Mederic ,
I'm sorry for sending out the original file (no changes). I'll attach the file with the transformations. Also, with respect to the selecting the first of the four weeks that starts on a Monday is also incorporated here. Here is the image below:

Here is the output:

Regards,

[Mederic]

@SundarRaj , no worries, thank you very much for this file.
I'll take the time to look at it tomorrow when I get back from work.
In any case, it looks good to me and I'll get back to you tomorrow evening.

Have a nice evening

Kind regards

[Mederic]

Hello @SundarRaj , @tharunkumarRTK 
Thank you very much for your suggestions.
As I will be looking at each of the responses in detail,
I will get back to you later today.
Thank you again.

Kind regards,

[SundarRaj]

Hi @Mederic ,
I'll attach the same file you sent with some transformations. Do let me know if that's you were looking for and if I misunderstood, do let me know. Thanks

Regards,

[tharunkumarRTK]

@Mederic 

As the expected result you mentioned in file you shared 

You can follow this appraoch

(I considered wednesday as week start day)

let
  Source = List.Dates(#date(2023,1,1),1100,#duration(1,0,0,0)),
  #"Converted to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed column type" = Table.TransformColumnTypes(#"Converted to table", {{"Column1", type date}}),
  #"Renamed columns" = Table.RenameColumns(#"Changed column type", {{"Column1", "Date"}}),
  #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Renamed columns", "Year", each Date.Year([Date])), {{"Year", Int64.Type}}),
  #"Added custom 1" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom", "Month Nr", each Date.Month([Date])), {{"Month Nr", Int64.Type}}),
  #"Added custom 2" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom 1", "Month Name", each Date.MonthName([Date])), {{"Month Name", type text}}),
  #"Added custom 3" = Table.TransformColumnTypes(Table.AddColumn(#"Added custom 2", "Week Nr", each if [Date] < Date.From((DateTime.LocalNow() - #duration(28,0,0,0))) then [Year] else Date.WeekOfYear([Date])), {{"Week Nr", Int64.Type}}),
  #"Added custom 4" = Table.AddColumn(#"Added custom 3", "Range", each if [Date] < Date.From((DateTime.LocalNow() - #duration(28,0,0,0))) then "" else Text.From (Date.Day(Date.StartOfWeek([Date], Day.Wednesday))) & "-" & Text.From (Date.Day(Date.EndOfWeek([Date], Day.Wednesday))))
in
  #"Added custom 4"

Output:

 

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!