Week count after&before New Year

[Anonymous]

Hello,

I am a newbie who's using Power BI for the first time, and find myself struggling over Week splitting when there is a year transition.

Indeed, I have a TIME table, whose columns are calculated like this: 

By default, Week 1 begins the first Monday of the new year (Spanish calendar, weeks are Mon-Sun).

The problem is that with the DAX configuration above, week 52 is absorbed by January (which is incorrect, it should fall in December):

Does anyone who if/how can Power BI set a standard formula in order to consider Week 1 the one starting on January 1st, and week 52 until 31st December?

Thanks a lot for any hint regarding this topic (I haven't been able to find a related tutorial). 

Regards,

Teresa

[Anonymous]

Hi @Anonymous ,

I have created a new sample, please refer to my pbix file to see if it helps you.

Column =
IF (
    'Table'[month] = 1
        && 'Table'[Weeknumber] > 10,
    MINX (
        FILTER (
            'Table',
            'Table'[Yearmonthnumber] = EARLIER ( 'Table'[Yearmonthnumber] )
        ),
        'Table'[Yearweek]
    ),
    IF (
        'Table'[month] = 12
            && 'Table'[Weeknumber] <= 10,
        MAXX (
            FILTER (
                'Table',
                'Table'[Yearmonthnumber] = EARLIER ( 'Table'[Yearmonthnumber] )
            ),
            'Table'[Yearweek] + 1
        ),
        'Table'[Yearweek]
    )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hello @Anonymous ,

Thanks for the advice. We found a workaround (basically filtering out January 1st 2023), but I keep the code in case the workaround has more impact than initially expected and we need to re-work on it.

Thanks a lot!

Regards,

Teresa

[andhiii079845]

Did this help?
https://dax.guide/weeknum/

WEEKNUM ( 'Date'[Date],  2 ), -- Week start on Mon

[Anonymous]

Hi @andhiii079845 ,

See the answer to previous comment: 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Week-count-after-amp-before-New-Year/m-p/3119... 

Thanks very much. 

Regards, 

Teresa

[FreemanZ]

hi @Anonymous 

try to change all 21 to 2, in line 19 and 20 of your screenshot. It is the second argument of all WEEKNUM function.

[Anonymous]

Hi @FreemanZ ,

Thank you for the tip. Unfortunately it didn't work on this case, as it took as Week 2 the week between January 2nd - 8th (in Spain week starts on Monday). It put January 1st (2023) as week 52 by the end of Januay.

We solved it by filtering out January 1st 2023 (it is not a big deal, as it's a day with 0 results). 

Thanks a lot.

Regards, 

Teresa