topic Re: DAX - Week Number - to start from 1st Jan in DAX Commands and Tips

DAX - Week Number - to start from 1st Jan

ElMeSaFeR — Sat, 23 Apr 2022 19:25:38 GMT

Hello!

I've used CALENDAR() in a new table and I've obtained a date column, I'd like to calculate a new column with DAX to obtain the week number ( 1 - 54 ), but with the following condition:

1) The first week of the year should be the 1st January for every year.

I've read similar posts and tried to apply and modify the DAX code to acheive that, but I haven't succeed.

Thanks in advance for your help

Re: DAX - Week Number - to start from 1st Jan

tackytechtom — Sun, 24 Apr 2022 11:44:00 GMT

Hi ElMeSaFeR ,

How about this:

Here the DAX that was used to create the calculated column:

WeekNumber = 
ROUNDDOWN (
    ( ( RANKX ( 
        FILTER ( 
            'date', 
            'date'[Year] = EARLIER ( 'date'[Year] )
        ),
        'date'[Dates],
        , ASC
        , DENSE
    ) + 6 ) ) / 7, 0 )

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: DAX - Week Number - to start from 1st Jan

ElMeSaFeR — Sun, 24 Apr 2022 12:36:37 GMT

Excellent, Thanks alot 🙂