Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to Solution.
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] )
Pbix as an attachment.
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] )
Pbix as an attachment.
Thank you. That worked nicely!
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))
Split your Date column in Power Query. You can use the DAX below:
Column =
VAR __Calendar =
ADDCOLUMNS(
CALENDAR(DATE([Date.2],1,1),DATE([Date.2],12,31)),
"Week",
WEEKNUM([Date])
)
RETURN
MINX(FILTER(__Calendar,[Week] = [Date.1]),[Date])