Hi,
I've been experimenting the relative date filters lately.
Especially selecting Last x calendar weeks.
Then I came upon some strange results which I don't understand completely.
So I have this Date Table where I have created a column [Week#] that gives me the week number from this formula:
YEAR([Date])&"-W"&WEEKNUM([Date])
I used an ISO notation so I don't have to make any calculations to have it sequenced properly over years.
I don't have the script for the date table. But let's say it has columns [Date] and [Week#].
Then I filter this table using [Date] field using a Realtive Date filter like this one: "Last 52 Calendar weeks".
Then I put a card visual showing the distinct count of [Week#].
And now it shows me 53. (wtf???)
I extracted this table showing you the start and end of each weeks and the week#:
| KPI Week # | Week Start | Week End |
| 2019-W46 | 11/10/2019 | 11/16/2019 |
| 2019-W47 | 11/17/2019 | 11/23/2019 |
| 2019-W48 | 11/24/2019 | 11/30/2019 |
| 2019-W49 | 12/1/2019 | 12/7/2019 |
| 2019-W50 | 12/8/2019 | 12/14/2019 |
| 2019-W51 | 12/15/2019 | 12/21/2019 |
| 2019-W52 | 12/22/2019 | 12/28/2019 |
| 2019-W53 | 12/29/2019 | 1/4/2020 |
| 2020-W01 | 12/29/2019 | 1/4/2020 |
| 2020-W02 | 1/5/2020 | 1/11/2020 |
| 2020-W03 | 1/12/2020 | 1/18/2020 |
| 2020-W04 | 1/19/2020 | 1/25/2020 |
| 2020-W05 | 1/26/2020 | 2/1/2020 |
| 2020-W06 | 2/2/2020 | 2/8/2020 |
| 2020-W07 | 2/9/2020 | 2/15/2020 |
| 2020-W08 | 2/16/2020 | 2/22/2020 |
| 2020-W09 | 2/23/2020 | 2/29/2020 |
| 2020-W10 | 3/1/2020 | 3/7/2020 |
| 2020-W11 | 3/8/2020 | 3/14/2020 |
| 2020-W12 | 3/15/2020 | 3/21/2020 |
| 2020-W13 | 3/22/2020 | 3/28/2020 |
| 2020-W14 | 3/29/2020 | 4/4/2020 |
| 2020-W15 | 4/5/2020 | 4/11/2020 |
| 2020-W16 | 4/12/2020 | 4/18/2020 |
| 2020-W17 | 4/19/2020 | 4/25/2020 |
| 2020-W18 | 4/26/2020 | 5/2/2020 |
| 2020-W19 | 5/3/2020 | 5/9/2020 |
| 2020-W20 | 5/10/2020 | 5/16/2020 |
| 2020-W21 | 5/17/2020 | 5/23/2020 |
| 2020-W22 | 5/24/2020 | 5/30/2020 |
| 2020-W23 | 5/31/2020 | 6/6/2020 |
| 2020-W24 | 6/7/2020 | 6/13/2020 |
| 2020-W25 | 6/14/2020 | 6/20/2020 |
| 2020-W26 | 6/21/2020 | 6/27/2020 |
| 2020-W27 | 6/28/2020 | 7/4/2020 |
| 2020-W28 | 7/5/2020 | 7/11/2020 |
| 2020-W29 | 7/12/2020 | 7/18/2020 |
| 2020-W30 | 7/19/2020 | 7/25/2020 |
| 2020-W31 | 7/26/2020 | 8/1/2020 |
| 2020-W32 | 8/2/2020 | 8/8/2020 |
| 2020-W33 | 8/9/2020 | 8/15/2020 |
| 2020-W34 | 8/16/2020 | 8/22/2020 |
| 2020-W35 | 8/23/2020 | 8/29/2020 |
| 2020-W36 | 8/30/2020 | 9/5/2020 |
| 2020-W37 | 9/6/2020 | 9/12/2020 |
| 2020-W38 | 9/13/2020 | 9/19/2020 |
| 2020-W39 | 9/20/2020 | 9/26/2020 |
| 2020-W40 | 9/27/2020 | 10/3/2020 |
| 2020-W41 | 10/4/2020 | 10/10/2020 |
| 2020-W42 | 10/11/2020 | 10/17/2020 |
| 2020-W43 | 10/18/2020 | 10/24/2020 |
| 2020-W44 | 10/25/2020 | 10/31/2020 |
| 2020-W45 | 11/1/2020 | 11/7/2020 |
From week 2019-W46 start date to 2020-W45 end date, it's 363 days, and 51.86 weeks.
I think I have found the reason why it is not calculating weeks properly.
Even though WEEKNUM formula seems to use ISO week numbering, it fails to implement correctly the week overlapping two years.
If you look at week 2019-W53, you'll notice that it only has 3 days and week 2020-W01 has 4 days.
Normally, week 2019-W53 would have been in fact Week 2020-W01 since it has a Thursday, as per ISO 8601.
But unfortunately, Power BI does not have a ISOWEEKNUM formula, and its WEEKNUM is not the same as the one in Excel.
Excel actually does have a 1st week of the year rule in its WEEKNUM formula. Which in 2019 and 2020 gives same results as ISOWEEKNUM. But once every 4 years, it is not inline with ISOWEEKNUM.
Power BI should have ISOWEEKNUM formula so we can apply proper ISO week numbers to our dates tables.
In the meantime, I'll be recreating the ISOWEEKNUM formula in Power BI in order to get 52 weeks when I ask for last 52 weeks.
So if anyone's already done this, please let me know.
Otherwise, I'll probably be posting my solution here.
