Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Finding date using Weeknum Weekday and Year

Hi Team,

I have weeknumber column WeekDay Colum and Year column, I have to find date for this.

Can you please help me.

 

Thanks!

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    According to your description, here's my solution, create a column.

    Column =
VAR _firstweek =
    7 - WEEKDAY ( DATE ( [Year], 1, 1 ) ) + 1
VAR _between = ( [Week] - 1 ) * 7
VAR _lastweek =
    SWITCH (
        [WeekDay],
        "Monday", 1,
        "Tuesday", 2,
        "Wedsday", 3,
        "Thursday", 4,
        "Friday", 5,
        "Saturday", 6,
        "Sunday", 7
    )
RETURN
    DATE ( [Year], 1, 1 ) + _firstweek + _between + _lastweek

    Get the correct result:

    I attach my sample below for your reference.

     

    Best regards,

    Community Support Team_yanjiang

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

     

6 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    3 years ago

    Anonymous 
    Can you provide the sample data and output?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      rajulshah 

      Sample data is 

      Week  WeekDay Year Req_Result

      41       Tuesday   2022       11/10/2023(DD_MM_YYYY)

       

      • rajulshah's avatar
        rajulshah
        Resident Rockstar
        3 years ago

        Anonymous 
        I have created a measure as follows. Maybe this can help you to reach what you need.

        SelectedDate =
VAR DateTable =
    CALENDARAUTO ()
VAR WeekNumber = 41
VAR WeekDayValue = "Tuesday"
VAR YearValue = 2023
RETURN
    MINX (
        FILTER (
            DateTable,
            FORMAT ( [Date], "dddd" ) = WeekDayValue
                && WEEKNUM ( [Date] ) = WeekNumber
                && YEAR ( [Date] ) = YearValue
        ),
        [Date]
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    rajulshah  I want to create a date colum based on weeknum weekday and year

     

     

    thanks !

    • rajulshah's avatar
      rajulshah
      Resident Rockstar
      3 years ago

      Anonymous 

      So instead of variables, you can use the columns in it. Please let me know if you find any issues.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi Anonymous ,

    According to your description, here's my solution, create a column.

    Column =
VAR _firstweek =
    7 - WEEKDAY ( DATE ( [Year], 1, 1 ) ) + 1
VAR _between = ( [Week] - 1 ) * 7
VAR _lastweek =
    SWITCH (
        [WeekDay],
        "Monday", 1,
        "Tuesday", 2,
        "Wedsday", 3,
        "Thursday", 4,
        "Friday", 5,
        "Saturday", 6,
        "Sunday", 7
    )
RETURN
    DATE ( [Year], 1, 1 ) + _firstweek + _between + _lastweek

    Get the correct result:

    I attach my sample below for your reference.

     

    Best regards,

    Community Support Team_yanjiang

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