cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fonlovedog207
Frequent Visitor

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?

Fonlovedog207_0-1674808220223.png

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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.

RicoZhou_0-1675155661485.png

 

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.

View solution in original post

2 REPLIES 2
RicoZhou
Community Support
Community Support

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.

RicoZhou_0-1675155661485.png

 

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.

Aurelio
Frequent Visitor

This code would do the job for you. 

 

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

Helpful resources

Announcements
T-Shirt Design Challenge 2023

Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors