Forum Discussion
Syndicate_Admin
3 years agoAdministrator
Convert format week to date format
Hello, good evening.
I would like to ask for help, I need to pass the next column week by date.
However, when using the following DAX, I have problems with weeks 1 to 9, since they only have 1 digit, try to add the 0 before, replacing for example the 1 by the 01, however, I change all the values where a 1 appears, example the 11 becomes a 101 or 110.
WeekStart =
MINX(
FILTER (
CALENDAR (
DATE ( LEFT ( [YEARWEAK], 4 ), 1, 1 ),
DATE ( LEFT ( [YEARWEAK], 4 ), 12, 31 )
),
WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( [YEARWEAK], 2 ) )
),
[Date]
)
The above is as follows, where weeks 1 to 9, are incorrect.
Thanks for reading!
aha, RETURN is missing
WeekStart =
VAR _len = LEN([YEARWEAK])VAR _value = IF(_len=6, 2, 1)RETURNMINX(FILTER (CALENDAR (DATE ( LEFT ( [YEARWEAK], 4 ), 1, 1 ),DATE ( LEFT ( [YEARWEAK], 4 ), 12, 31 )),WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( [YEARWEAK], _value ) )),[Date])
4 Replies
- FreemanZSuper Usertry to add a LEN condition, like this:WeekStart =VAR _len = LEN([YEARWEAK])VAR _value = IF(_len=6, 2, 1)MINX(FILTER (CALENDAR (DATE ( LEFT ( [YEARWEAK], 4 ), 1, 1 ),DATE ( LEFT ( [YEARWEAK], 4 ), 12, 31 )),WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( [YEARWEAK], _value ) )),[Date])
- Syndicate_AdminAdministrator
It throws me the following error, you know why it could be?
- FreemanZSuper User
aha, RETURN is missing
WeekStart =
VAR _len = LEN([YEARWEAK])VAR _value = IF(_len=6, 2, 1)RETURNMINX(FILTER (CALENDAR (DATE ( LEFT ( [YEARWEAK], 4 ), 1, 1 ),DATE ( LEFT ( [YEARWEAK], 4 ), 12, 31 )),WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( [YEARWEAK], _value ) )),[Date])