powerbidaxdata-analysispowerbi-desktopbusiness-intelligence

How does dax week and year handle 31 dec 2023?


I'm creating calendar table.

When I use YEAR dax on a date, say 31 dec 2023, then it will return year as 2023.

But if I use WEEKNUM dax on this date,

Returns the week number for the given date according to the return_type value. The week number indicates where the week falls numerically within a year.

When I try WEEKNUM(31st dec 2023) then I get 53. Shouldn't this be 1?

And YEAR(31st dec 2023) gives me 2023. Shouldn't this be 2024? As this date is a Sunday and falls in week one of 2024 when analysing by week I want this to be week 1. I'm confused is this possible to do?


Solution

  • I presume you mean WEEKNUM()?

    Weeks can have arbitrary rules applied to them as they don't align nicely with our calendar (see here for the pattern: https://www.daxpatterns.com/week-related-calculations/).

    At the year cross over points, you can choose how weeks are treated (and subsequent week numbers calculated) by using the second argument. i.e. it is a business decision for you to make rather than a hard technical rule.

    enter image description here

    enter image description here