Forum Discussion

george_o0802's avatar
george_o0802
Regular Visitor
1 year ago
Solved

Calendar Date Problem! Only been given Week number and Day (Monday, Tuesday, Wednesday)

I have recently been given some data to build a dashboard from. It's pretty horrendous but one of the big offenders is that there is no date in the data.

We have week numbers of the week commencing Monday in the format of YYYYWW (e.g., 202301, 202433, 202506 etc.).
We also have columns that have sales val Monday, Sales val Tuesday and the same for volume.

I've pivoted the columns and split out the YearWeek column so I'm left with 3 columns: Year, Week Number, and the Day.

From this, how do I calculate the dates of each of the days so I can actually use this data?

Note:
Deepseek gave this answer, ChatGPT gave something similar, it's wrong. 

Date.AddDays(
    #date([Year], 1, 1),
    (7 * ([Week Number] - 1)) - Date.DayOfWeek(#date([Year], 1, 1), Day.Monday)
)

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi george_o0802 

     

    Thanks for the reply from Greg_Deckler .

     

    I created sample data and did the following test, I hope it can help you.

    Create a calculated column as follows

     

    Date = 
VAR _WeekStartDate = DATE ( [Year], 1, 1 ) + ( [WeekNum] - 1 ) * 7 - WEEKDAY ( DATE ( [Year], 1, 1 ), 2 ) + 1
RETURN
SWITCH (
    [Day],
    "Monday", _WeekStartDate,
    "Tuesday", _WeekStartDate + 1,
    "Wednesday", _WeekStartDate + 2,
    "Thursday", _WeekStartDate + 3,
    "Friday", _WeekStartDate + 4,
    "Saturday", _WeekStartDate + 5,
    "Sunday", _WeekStartDate + 6
)

     

     

    Output:

    In my test, the week starts on Monday.

     

    Best Regards,
    Yulia Xu

     

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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    george_o0802 Here's a potential DAX solution:

    Column = 
    VAR __Year = [Year]
    VAR __Weeknum = [Weeknum]
    VAR __Table = 
        ADDCOLUMNS(
            CALENDAR( DATE( __Year, 1, 1 ), DATE( __Year, 12, 31 ) ),
            "Weeknum", WEEKNUM( [Date], 2 )
        )
    VAR __Result = MINX( FILTER( __Table, [Year] = __Year && [Weeknum] = __Weeknum ), [Date] )
RETURN
    __Result
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi george_o0802 

     

    Thanks for the reply from Greg_Deckler .

     

    I created sample data and did the following test, I hope it can help you.

    Create a calculated column as follows

     

    Date = 
VAR _WeekStartDate = DATE ( [Year], 1, 1 ) + ( [WeekNum] - 1 ) * 7 - WEEKDAY ( DATE ( [Year], 1, 1 ), 2 ) + 1
RETURN
SWITCH (
    [Day],
    "Monday", _WeekStartDate,
    "Tuesday", _WeekStartDate + 1,
    "Wednesday", _WeekStartDate + 2,
    "Thursday", _WeekStartDate + 3,
    "Friday", _WeekStartDate + 4,
    "Saturday", _WeekStartDate + 5,
    "Sunday", _WeekStartDate + 6
)

     

     

    Output:

    In my test, the week starts on Monday.

     

    Best Regards,
    Yulia Xu

     

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