Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Change Week Number Display Format

Hi!

 

i am trying to communicate week number in a different format than the default, which is just a number. i would like to instead of displaying "1" for the first week of the year, display "Jan 1" with the 1 being the first day of that week. the next week would show "Jan 8"

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    Please try the following dax

    Week Start Date = 
VAR CurrentDate = 'Table'[Date]  
VAR YearStartDate = DATE(YEAR(CurrentDate), 1, 1)
VAR FirstDayOfWeek = WEEKDAY(YearStartDate, 2) - 1
VAR AdjustedYearStart = YearStartDate - FirstDayOfWeek
VAR WeekNum = WEEKNUM(CurrentDate, 2) - 1
RETURN
AdjustedYearStart + (WeekNum * 7)

     

    Here is the result you want

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

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

5 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    If you've got a date table, then you can create a new column based on the week number - i.e 

    Start of Week = CALCULATE(MIN('Date Table'[Week Number]), ALLEXCEPT('Date Table', 'Date Table'[Week Number]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This just printed week number again

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    In your Dates table add this measure on Power Query:


    "= Table.AddColumn(#"Name of step above", "Week New Format", each Text.Combine({Date.ToText(Date.StartOfWeek([Date]), "MMM"), " ", Number.ToText(Date.Day(Date.StartOfWeek([Date])))}),
    type text)"

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I was not able to figure out the syntax, but the below worked for me. 

       

      'Date Table'[Due Date] - WEEKDAY('Date Table'[Due Date],2) + 1
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    Please try the following dax

    Week Start Date = 
VAR CurrentDate = 'Table'[Date]  
VAR YearStartDate = DATE(YEAR(CurrentDate), 1, 1)
VAR FirstDayOfWeek = WEEKDAY(YearStartDate, 2) - 1
VAR AdjustedYearStart = YearStartDate - FirstDayOfWeek
VAR WeekNum = WEEKNUM(CurrentDate, 2) - 1
RETURN
AdjustedYearStart + (WeekNum * 7)

     

    Here is the result you want

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

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