Change Week Number Display Format

[roeder11]

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"

[Anonymous · Accepted solution]

Hi @roeder11 

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.

[_AAndrade]

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)"

[roeder11]

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

[vicky_]

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]))

[roeder11]

This just printed week number again