Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

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!

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    aha, RETURN is missing

     

    WeekStart =

    VAR _len = LEN([YEARWEAK])
    VAR _value = IF(_len=6, 2, 1)
    RETURN
    MINX(
        FILTER (
            CALENDAR (
                DATE ( LEFT ( [YEARWEAK], 4 ), 1, 1 ),
                DATE ( LEFT ( [YEARWEAK], 4 ), 12, 31 )
            ),
            WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( [YEARWEAK], _value ) )
        ),
        [Date]
    )

4 Replies

  • try 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]
    )
      • FreemanZ's avatar
        FreemanZ
        Super User

        aha, RETURN is missing

         

        WeekStart =

        VAR _len = LEN([YEARWEAK])
        VAR _value = IF(_len=6, 2, 1)
        RETURN
        MINX(
            FILTER (
                CALENDAR (
                    DATE ( LEFT ( [YEARWEAK], 4 ), 1, 1 ),
                    DATE ( LEFT ( [YEARWEAK], 4 ), 12, 31 )
                ),
                WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( [YEARWEAK], _value ) )
            ),
            [Date]
        )