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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Unmatick
New Member

ISO week number to normal date?

Hello, I work for a large company in the automotive industry, and we measure time using the ISO week and ISO day in our systems.

Today's date is 2023-12-05, which translates to ISO week 49, day 2 (49.2).

I've been attempting to make Excel or Power BI understand this correlation, but so far, I haven't been successful.
Do you know of any solutions that could help me figure this out?

2 ACCEPTED SOLUTIONS
ray_codex
Resolver I
Resolver I

Hi, 

 

This measure should do it. Liked the concept, so tested it:

 

 

 

 

test = 
VAR add_col =
    ADDCOLUMNS (
        'Date',
        "_isoweek", WEEKNUM ( 'date'[Date], 21 ),
        "_isoday",
            SWITCH (
                TRUE,
                WEEKNUM ( 'date'[Date] - 1, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 1, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 1,
                WEEKNUM ( 'date'[Date] - 2, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 2, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 2,
                WEEKNUM ( 'date'[Date] - 3, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 3, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 3,
                WEEKNUM ( 'date'[Date] - 4, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 4, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 4,
                WEEKNUM ( 'date'[Date] - 5, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 5, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 5,
                WEEKNUM ( 'date'[Date] - 6, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 6, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 6,
                WEEKNUM ( 'date'[Date] - 7, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 7, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 7,
                BLANK ()
            )
    )
RETURN
    AVERAGEX ( add_col, [_isoweek] ) & "."
        & AVERAGEX ( add_col, [_isoday] )

 

 

 

 

If it works please accept the solution 😄

btw, to explain what it does, WEEKNUM([date], 21) returns the isoweek. what I did is add a column to the date table and check for every date if the isoweek is the same of the previous week for the date - 1 for the first day, date - 2 for the second day etc. For week 1 did the same, but checked if the isoweek is a higher number that the date's isoweek. 

You can also do this in Excel. Add a tab with all dates in a column, use this logic to add a second column next to it  and use x.lookup to find the value by lookup up a date in the first column returning the value of the second column.

View solution in original post

some_bih
Super User
Super User

Hi @Unmatick 

Possible solution with WEEKNUM funtion. Change return_type as you wish

https://dax.do/ccgdCByykFg3vM/ 

 

Reference to documentation: https://learn.microsoft.com/en-us/dax/weeknum-function-dax 





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

Proud to be a Super User!






View solution in original post

2 REPLIES 2
some_bih
Super User
Super User

Hi @Unmatick 

Possible solution with WEEKNUM funtion. Change return_type as you wish

https://dax.do/ccgdCByykFg3vM/ 

 

Reference to documentation: https://learn.microsoft.com/en-us/dax/weeknum-function-dax 





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

Proud to be a Super User!






ray_codex
Resolver I
Resolver I

Hi, 

 

This measure should do it. Liked the concept, so tested it:

 

 

 

 

test = 
VAR add_col =
    ADDCOLUMNS (
        'Date',
        "_isoweek", WEEKNUM ( 'date'[Date], 21 ),
        "_isoday",
            SWITCH (
                TRUE,
                WEEKNUM ( 'date'[Date] - 1, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 1, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 1,
                WEEKNUM ( 'date'[Date] - 2, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 2, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 2,
                WEEKNUM ( 'date'[Date] - 3, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 3, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 3,
                WEEKNUM ( 'date'[Date] - 4, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 4, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 4,
                WEEKNUM ( 'date'[Date] - 5, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 5, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 5,
                WEEKNUM ( 'date'[Date] - 6, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 6, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 6,
                WEEKNUM ( 'date'[Date] - 7, 21 )
                    = WEEKNUM ( 'date'[Date], 21 ) - 1
                    || WEEKNUM ( 'date'[Date] - 7, 21 )
                        > WEEKNUM ( 'date'[Date], 21 ), 7,
                BLANK ()
            )
    )
RETURN
    AVERAGEX ( add_col, [_isoweek] ) & "."
        & AVERAGEX ( add_col, [_isoday] )

 

 

 

 

If it works please accept the solution 😄

btw, to explain what it does, WEEKNUM([date], 21) returns the isoweek. what I did is add a column to the date table and check for every date if the isoweek is the same of the previous week for the date - 1 for the first day, date - 2 for the second day etc. For week 1 did the same, but checked if the isoweek is a higher number that the date's isoweek. 

You can also do this in Excel. Add a tab with all dates in a column, use this logic to add a second column next to it  and use x.lookup to find the value by lookup up a date in the first column returning the value of the second column.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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