Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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?
Solved! Go to 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.
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
Proud to be a 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
Proud to be a Super User!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
21 | |
17 | |
16 | |
11 | |
7 |
User | Count |
---|---|
26 | |
25 | |
12 | |
12 | |
12 |