Thank you @BI Gorilla for this video! How did you find this solution? Curious if you had help from the internet or if you were able to develop it completely from scratch.
Hey Yon, For this particular setup, part of the 445 logic is inspired by Ken Puls, he did some work on this. All else I developed with some experimentation. For instance, the option to switch between 445, 454 and 544 was something that I had never seen anywhere before, but is mightily useful.
I am trying to learn PQ and understanding M language and not easy with the syntax so I will just ask in Excel Formula terms, the point at 5:17 while creating Week Index. Given that A1# is a sequence of 1..364, whats the difference between following two: =ROUNDUP(A2#/7,) =ROUNDDOWN((A2# -1)/7+1,) What's the reason for inclining towards the second cumbersome formula?
Muhammad, Actually that is a terrific suggestion. I'm not sure why I didn't think of that easier version to begin with. This gives the same result: Number.RoundUp( [FW_DayIndex] / 7 ) I've added it to the written blog at: gorilla.bi/power-query/445-calendar/
I'm going to hav to have a read and come back for the period index, btw i know you've done videos and cover them in the book , but recently came across using custom comparer option in group; so no Unit group > 50 Table.Group(dex, {"Unit", "Index"}, {{"Count", each _ }} , 0, (x,y)=> Number.From( List.Sum( List.Range( dex [Unit], x [Index] , ( y[Index] - x [Index] ) +1)) > 50 )) still not got to grips with how x , y relate within a range .
William, That's great. Just be careful when creating a custom comparer function. Try to have a symmetric comparer function for the GroupKind.Global (which Table.Group uses by default) For instance: (x, y) => Value.Compare( Date.StartOfQuarter( x[Date] ), Date.StartOfQuarter( y[Date] ) ) ( x, y ) => Value.Compare ( Text.Lower ( x[Name] ), Text.Lower ( y[Name] ) ) When using GroupKind.Local, you can use the simplified expression, which could use the Number.From setup you sent. You may run into inconsistent results otherwise. More info on this I wrote here: gorilla.bi/power-query/table-group-custom-comparers/
I haven't worked with 445 calendars that have that 53rd week in there. Some people opt for simply setting a starting date of the year. If there is a recurring pattern, with the 53rd week, that would be standard in business, I'm happy to look into it. Do you have any resources explaining its logic?
@@BIGorilla I worked at Symantec on NASDAQ for years. So the 4 4 6 was added every 5 or 6 years because the quarter end gets too far away (early quarter end) from the natural end of quarter. This would especially become an issue in Sales where negotiations rely on a "get this deal but expires end of month" type thing. If you run your start and end date for a number of years you'll see that natural movement forward because there are only 364 days counted. In the case of Symantec, we added the 6th week in the first quarter to push the EOQ date past the month end point as that was beneficial to have an internal EOQ date that was past the EOM date.
@@BIGorilla I work a lot with the 445 and for the 53rd week issue we would either combine the 52nd/53rd together or use a 446 in the final quarter. Logically, I favour the 446 but requires some extra calculation in yoy dax to account for them.
Want to copy code instead? You can find the written article at: gorilla.bi/power-query/445-calendar/
Cheers! Rick
Thank you @BI Gorilla for this video! How did you find this solution? Curious if you had help from the internet or if you were able to develop it completely from scratch.
Hey Yon,
For this particular setup, part of the 445 logic is inspired by Ken Puls, he did some work on this. All else I developed with some experimentation. For instance, the option to switch between 445, 454 and 544 was something that I had never seen anywhere before, but is mightily useful.
@@BIGorilla Awesome. Thanks for the tip! Great video
I am trying to learn PQ and understanding M language and not easy with the syntax so I will just ask in Excel Formula terms, the point at 5:17 while creating Week Index.
Given that A1# is a sequence of 1..364, whats the difference between following two:
=ROUNDUP(A2#/7,)
=ROUNDDOWN((A2# -1)/7+1,)
What's the reason for inclining towards the second cumbersome formula?
Muhammad,
Actually that is a terrific suggestion. I'm not sure why I didn't think of that easier version to begin with.
This gives the same result: Number.RoundUp( [FW_DayIndex] / 7 )
I've added it to the written blog at: gorilla.bi/power-query/445-calendar/
I'm going to hav to have a read and come back for the period index,
btw i know you've done videos and cover them in the book , but recently came across using
custom comparer option in group; so no Unit group > 50
Table.Group(dex, {"Unit", "Index"}, {{"Count", each _ }} ,
0, (x,y)=> Number.From( List.Sum( List.Range( dex [Unit],
x [Index] , ( y[Index] - x [Index] ) +1)) > 50 ))
still not got to grips with how x , y relate within a range .
William,
That's great. Just be careful when creating a custom comparer function. Try to have a symmetric comparer function for the GroupKind.Global (which Table.Group uses by default)
For instance:
(x, y) => Value.Compare( Date.StartOfQuarter( x[Date] ), Date.StartOfQuarter( y[Date] ) )
( x, y ) => Value.Compare ( Text.Lower ( x[Name] ), Text.Lower ( y[Name] ) )
When using GroupKind.Local, you can use the simplified expression, which could use the Number.From setup you sent. You may run into inconsistent results otherwise. More info on this I wrote here: gorilla.bi/power-query/table-group-custom-comparers/
@@BIGorilla Yes, not sure about the science, but when using 'global' I have got in a mess.
just to add ; can't take credit for initial idea.
But how about the 53 week year every 5-6 years? Last was 2023, next in 2029?
I haven't worked with 445 calendars that have that 53rd week in there. Some people opt for simply setting a starting date of the year. If there is a recurring pattern, with the 53rd week, that would be standard in business, I'm happy to look into it. Do you have any resources explaining its logic?
@@BIGorilla I worked at Symantec on NASDAQ for years. So the 4 4 6 was added every 5 or 6 years because the quarter end gets too far away (early quarter end) from the natural end of quarter. This would especially become an issue in Sales where negotiations rely on a "get this deal but expires end of month" type thing. If you run your start and end date for a number of years you'll see that natural movement forward because there are only 364 days counted. In the case of Symantec, we added the 6th week in the first quarter to push the EOQ date past the month end point as that was beneficial to have an internal EOQ date that was past the EOM date.
@@BIGorilla I work a lot with the 445 and for the 53rd week issue we would either combine the 52nd/53rd together or use a 446 in the final quarter. Logically, I favour the 446 but requires some extra calculation in yoy dax to account for them.