Forum Discussion

Fonlovedog207's avatar
Fonlovedog207
Frequent Visitor
3 years ago
Solved

HOW TO CREATE DATE(Thursday OF WEEK) FORM YEAR-MONTH

I have column year-week  ,i need to create date form year week(Thursday of week)

in Qlikview i used function "makedate" but  i don't know in PBI used any function?

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Fonlovedog207 ,

     

    Try this code to create a calculated table.

    Calendar Only Thursday =
VAR _Calendar_Only_Thursday =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2023, 12, 31 ) ),
            "Year", YEAR ( [Date] )
        ),
        WEEKDAY ( [Date], 2 ) = 4
    )
VAR _AddRank =
    ADDCOLUMNS (
        _Calendar_Only_Thursday,
        "Rank",
            RANKX (
                FILTER ( _Calendar_Only_Thursday, [Year] = EARLIER ( [Year] ) ),
                [Date],
                ,
                ASC,
                DENSE
            )
    )
RETURN
    SELECTCOLUMNS (
        _AddRank,
        "Year Week",
            [Year] * 100 + [Rank],
        "Date Need", [Date]
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Aurelio's avatar
    Aurelio
    Frequent Visitor
    3 years ago

    This code would do the job for you. 

     

    YEARWEEK = YEAR('Date'[Date])& WEEKNUM('Date'[Date],1)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Fonlovedog207 ,

     

    Try this code to create a calculated table.

    Calendar Only Thursday =
VAR _Calendar_Only_Thursday =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2023, 12, 31 ) ),
            "Year", YEAR ( [Date] )
        ),
        WEEKDAY ( [Date], 2 ) = 4
    )
VAR _AddRank =
    ADDCOLUMNS (
        _Calendar_Only_Thursday,
        "Rank",
            RANKX (
                FILTER ( _Calendar_Only_Thursday, [Year] = EARLIER ( [Year] ) ),
                [Date],
                ,
                ASC,
                DENSE
            )
    )
RETURN
    SELECTCOLUMNS (
        _AddRank,
        "Year Week",
            [Year] * 100 + [Rank],
        "Date Need", [Date]
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.