Change week number to a date

[SophRow]

Hi experts,

I currently have this date table and am plotting 'Week' on an x-axis.

My end users want me to change 'Week X' to the first day of that week, for example, week 31 would be 28 July 2024.

Any tips here?

[danextian · Accepted solution]

Hi @SophRow ,

Create a week number column then calculate the first day of each week number.

Week =
VAR FirstDay =
    CALCULATE ( MIN ( dates[date] ), ALLEXCEPT ( dates, dates[Week number] ) )
RETURN
    IF (
        FirstDay = dates[date],
        FORMAT ( dates[date], "dd/mm/yyyy" ),
        "Week " & dates[Week number]
    )

As the calculated column above will generate text strings, the values will be sorted alphabetically thus create a sort-by column.

Week Sort =
VAR FirstDay =
    CALCULATE ( MIN ( dates[date] ), ALLEXCEPT ( dates, dates[Week number] ) )
RETURN
    IF (
        FirstDay = dates[date],
        dates[Week number],
        dates[Week number] + 0.01
    )

[Ray_Minds]

Hi @SophRow 

1. Add a 'Start of Week' Column to Your Date Table:
First, you'll need to create a new column in your Date table that represents the start date of
each week.

This formula calculates the Monday of the week for each date, assuming your week starts on
Monday (which is common in many regions). If your week starts on a different day, you can
adjust the 2 in the WEEKDAY function accordingly (e.g., 1 for Sunday, 7 for Saturday).
2. Replace 'Week' with 'Start of Week' in Your Visual:
      Go to the visual where you're using 'Week' on the x-axis.
      Replace the 'Week' field with the new 'StartOfWeek' column.
3. Formatting the Date
To ensure the date is displayed in a user-friendly way, you might want to format the
'StartOfWeek' column as "dd MMM yyyy" (e.g., "28 July 2024").

Make sure to sort the x-axis by the 'StartOfWeek' column to ensure that the weeks are displayed
in the correct chronological order.
This approach will display the first day of the week instead of the week number, making it
clearer for your end users.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[shafiz_p · Accepted solution]

Hi @SophRow  Please find below image to calculate week start date.

Hope this helps!!
If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[danextian · Accepted solution]

Hi @SophRow ,

Create a week number column then calculate the first day of each week number.

Week =
VAR FirstDay =
    CALCULATE ( MIN ( dates[date] ), ALLEXCEPT ( dates, dates[Week number] ) )
RETURN
    IF (
        FirstDay = dates[date],
        FORMAT ( dates[date], "dd/mm/yyyy" ),
        "Week " & dates[Week number]
    )

As the calculated column above will generate text strings, the values will be sorted alphabetically thus create a sort-by column.

Week Sort =
VAR FirstDay =
    CALCULATE ( MIN ( dates[date] ), ALLEXCEPT ( dates, dates[Week number] ) )
RETURN
    IF (
        FirstDay = dates[date],
        dates[Week number],
        dates[Week number] + 0.01
    )