ISO week number to normal date?

[Unmatick]

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?

[ray_codex · Accepted solution]

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.

[some_bih · Accepted solution]

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 

[ray_codex · Accepted solution]

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.