Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tere_or
Regular Visitor

Week count after&before New Year

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: 

tere_or_0-1678289877516.png

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):

tere_or_1-1678290109332.png

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

1 ACCEPTED SOLUTION

Hello @v-rongtiep-msft ,

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

View solution in original post

6 REPLIES 6
v-rongtiep-msft
Community Support
Community Support

Hi @tere_or ,

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]
    )
)

vpollymsft_0-1678849069569.png

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.

 

Hello @v-rongtiep-msft ,

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
Super User
Super User

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


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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Super User
Super User

hi @tere_or 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors