Week definition due to the end and start of the year

[eduardosilvin3]

Hello All, I have this calc column, but the start dates are not correct, because when the year is starting, the Week Text must says:

-for dec-27 to dec-31:  "W #53 Dec-27 / Dec-31" 

-for jan1 and jan2: "W #1 Jan-01 / Jan-02" 

Same for next years... any ideas?

[Fowmy · Accepted solution]

@eduardosilvin3 

Create a calculated column as follows:

Week Text = 
VAR __T = FILTER( 'Calendar' , Calendar[Week Year] = EARLIER( 'Calendar'[Week Year] ) )
VAR __MinDate = MINX(__T , Calendar[Date] )
VAR __MaxDate = MAXX(__T , Calendar[Date] )
RETURN
    "W #" & Calendar[Week Num] & " " & FORMAT( __MinDate , "Mmm-yy" ) & "/ " & FORMAT( __MaxDate , "Mmm-yy" )

[Fowmy · Accepted solution]

@eduardosilvin3 

Create a calculated column as follows:

Week Text = 
VAR __T = FILTER( 'Calendar' , Calendar[Week Year] = EARLIER( 'Calendar'[Week Year] ) )
VAR __MinDate = MINX(__T , Calendar[Date] )
VAR __MaxDate = MAXX(__T , Calendar[Date] )
RETURN
    "W #" & Calendar[Week Num] & " " & FORMAT( __MinDate , "Mmm-yy" ) & "/ " & FORMAT( __MaxDate , "Mmm-yy" )

[eduardosilvin3]

Working great, thanks!

[VahidDM]

Can you share the details of the week start date and end date please?

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[eduardosilvin3]

sure sorry:

Week Start Date = (SELECTEDVALUE('Calendar'[Date]) - WEEKDAY(SELECTEDVALUE('Calendar'[Date]),2) +1)

Week End Date = (SELECTEDVALUE('Calendar'[Date]) - WEEKDAY(SELECTEDVALUE('Calendar'[Date]),2) +7)

[VahidDM · Accepted solution]

try this @eduardosilvin3

Week Text =
VAR CurrentWeekYear = Calendar[Week Year]
VAR WeekStart = CALCULATE(MIN(Calendar[Date]), ALLEXCEPT(Calendar, Calendar[Week Year], Calendar[Week Num]))
VAR WeekEnd = CALCULATE(MAX(Calendar[Date]), ALLEXCEPT(Calendar, Calendar[Week Year], Calendar[Week Num]))
RETURN
"W #" & Calendar[Week Num] & " " & FORMAT(WeekStart, "MMM-yy") & " / " & FORMAT(WeekEnd, "MMM-yy")

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[eduardosilvin3]

Working great, thanks!