Forum Discussion

dapperscavenger's avatar
dapperscavenger
Icon for Helper V rankHelper V
6 years ago
Solved

Convert iso (wwyyyy) to date?

My report brings in the date format as ww.yyyy (e.g. 50.2019, 06.2020)   How can I convert this to a date format? (e.g. 09-12-2019, 05-02-2020)    
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi @dapperscavenger ,

    Please create a calculated column as shown below to work on it.

    Column = 
VAR year =
    RIGHT ( 'Table'[wwyyyy], 4 )
VAR weeknum =
    VALUE ( LEFT ( 'Table'[wwyyyy], 2 ) )
VAR datetable =
    CALENDAR ( DATE ( year, 1, 1 ), DATE ( year, 12, 31 ) )
RETURN
    MINX ( FILTER ( datetable, WEEKNUM ( [Date], 2 ) = weeknum ), [Date] )

    Capture.PNG

    Pbix as an attachment.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Assuming this is a single value

new column
var _minYear =date(year(mid([column],3,4))1,1)
var _maxYear =date(year(right([column],4))1,1)
return
Week = ((_minYear +(-1*weekday(_minYear)+1)) +7*left([column],2)) & "," & ((_maxYear +(-1*weekday(_maxYear)+1)) +7*mid([column],10,2))