Forum Discussion

asheyashey's avatar
asheyashey
Icon for Helper I rankHelper I
1 year ago
Solved

My X axis date column in weeks

The image is attached. I'm able to get it in Week "No" (month) format but not able to sort as in show in the above image. Each WeekNo should be from the start of the day which is Monday. Ple...
Need Help
Show and Tell
Tutorial Requests
  • Shahid12523's avatar
    Shahid12523
    1 year ago

    Create a Week Start Date (Monday)


    WeekStartDate =
    'Date'[Date] - WEEKDAY('Date'[Date], 2) + 1


    -This ensures every date maps to the Monday of its week.
    -WEEKDAY(..., 2) makes Monday = 1, Sunday = 7

     

    2. Generate a Sortable Week Index Across Years


    WeekIndex =
    YEAR('Date'[WeekStartDate]) * 100 + WEEKNUM('Date'[WeekStartDate], 2)


    - This gives you values like 202401, 202402, etc.
    - Ensures proper chronological sorting across years.

     

    3. Create Display Format: WeekNo (Month)


    WeekLabel =
    "W" & WEEKNUM('Date'[WeekStartDate], 2) & " (" & FORMAT('Date'[WeekStartDate], "MMM") & ")"

     

    - Example: W32 (Aug)

     

    4. Sort the Display Column by WeekIndex


    In Power BI:
    - Go to the WeekLabel column.
    - Use "Sort by Column" → select WeekIndex.

MohamedFowzan1's avatar
MohamedFowzan1
Icon for Super User rankSuper User
1 year ago

Hi asheyashey 

Adding on to the same concept mentioned by rohit1991 

 

Have 2 columns

Week Start = 'Date'[Date] - WEEKDAY('Date'[Date], 2) + 1
YearWeek = YEAR('Date'[Week Start]) & FORMAT(WEEKNUM('Date'[Week Start], 2), "00")

 
Within the X Axis use the label:

Week Label = "Week " & FORMAT(WEEKNUM('Date'[Week Start], 2), "00") & " (" & FORMAT('Date'[Week Start], "mmm dd") & ")"

 
Make sure to do the sort either in the visual or within the model pane by selecting the column and sort it by the other.

  • asheyashey's avatar
    asheyashey
    Icon for Helper I rankHelper I
    1 year ago

    Thank you for reply.
    Helped a lot