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
sw123
Helper III
Helper III

Problems with week number

Hi,

 

I have a date table with a week year column like this:

 

"Week year", YEAR([Date]) & " "& WEEKNUM([Date], 21)
 
In our calender (ISO 8601) Januari 1st and 2nd 2022 is part of week 52 2021. My date calender sets Januari 1st and 2nd to week 52 2022, which is of course totally wrong. How can I fix this?
 
Thanx in advance!
1 ACCEPTED SOLUTION
Samarth_18
Community Champion
Community Champion

Hi @sw123 ,

 

Try this:-

Column =
VAR week_num =
    1+ INT (
            DIVIDE (
                (
                    [Date] - WEEKDAY ( [Date], 16 ) + 1
                )
                    - ( DATE ( YEAR ( [Date] ), 1, 1 ) ),
                7
            )
        )
RETURN
    "Week year " & YEAR ( [Date] ) & " " & week_num

 

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

2 REPLIES 2
Samarth_18
Community Champion
Community Champion

Hi @sw123 ,

 

Try this:-

Column =
VAR week_num =
    1+ INT (
            DIVIDE (
                (
                    [Date] - WEEKDAY ( [Date], 16 ) + 1
                )
                    - ( DATE ( YEAR ( [Date] ), 1, 1 ) ),
                7
            )
        )
RETURN
    "Week year " & YEAR ( [Date] ) & " " & week_num

 

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

ALLUREAN
Solution Sage
Solution Sage

Hi, @sw123 

 

Try this calendar dax script (you need to play with the iso parameters also):

https://www.dropbox.com/s/qo20q59mbsz60av/Custom%20Calendar%20%28ISO%2C%20445%2C%20etc%29.txt?dl=0

or this can be also helpful:

https://blog.gbrueckl.at/2012/04/iso-8601-week-in-dax/

 

 




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


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




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.