Concat Date Column to show in correct week order

[ctedesco3307]

Hello All: 

I have a calculated column in my calendar table that adds from and  to the Start an End Date

RepWeek = "From"&" "&TDate[WeekStartDate]&" "&"To"&" "&TDate[WeekEndDate]

For the visual (matrix). How can I make it show in the right date order by week?  

Thanks in advance!! 

[Greg_Deckler · Accepted solution]

@ctedesco3307 

[Greg_Deckler · Accepted solution]

@ctedesco3307 According to your image, you need to sort RepWeek by Week I am thinking. It looks like you are setting sort by on TDate and not sure why.

[ctedesco3307]

@Greg_Deckler  - that's what I was looking for but I couldn't find the option to do that. Where is it? thx. 

[Greg_Deckler · Accepted solution]

@ctedesco3307 

[ctedesco3307]

@Greg_Deckler well duh, I should have found that ... thank you but it didn't work for me. 

[Greg_Deckler]

@ctedesco3307 Did you get an error?

[ctedesco3307]

@Greg_Deckler  no error, it just doesn't work. look at my pic in last post - the dates are still out of order 

thx

[Greg_Deckler · Accepted solution]

@ctedesco3307 According to your image, you need to sort RepWeek by Week I am thinking. It looks like you are setting sort by on TDate and not sure why.

[ctedesco3307]

 @Greg_Deckler  Ah! Ok – so when I do that I do get an error, which is this:

Looking into it – it looks like my formula to set the WeekStartDate and WeekEndDate isn’t working right.  What those 2 columns are supposed to show, is the Sunday(WeekStartDate) and Saturday(WeekEndDate) for the Date column

Here are my formulas – it looks like when Date is Sunday the formula calculates the prior Sunday

WeekStartDate = TDate[Date] -  WEEKDAY(TDate[Date],2)

WeekEndDate = TDate[Date] -  WEEKDAY(TDate[Date],2)+6

[Greg_Deckler · Accepted solution]

@ctedesco3307 OK, so I have week starting and ending measures: Week Starting - Microsoft Power BI Community

Those are pretty long and complicated though so let's see if we can create better ones: 

WeekStartDate = TDate[Date] -  (WEEKDAY(TDate[Date],1) -1)
WeekEndDate = WeekStartDate + 6

[ctedesco3307]

@Greg_Deckler This worked! thank you so much for your patience- this is exactly what I was looking for. I appreciate all your help here!  

[ctedesco3307]

 @Greg_Deckler  Ah! Ok – so when I do that I do get an error, which is this:

Looking into it – it looks like my formula to set the WeekStartDate and WeekEndDate isn’t working right.  What those 2 columns are supposed to show, is the Sunday(WeekStartDate) and Saturday(WeekEndDate) for the Date column

Here are my formulas – it looks like when Date is Sunday the formula calculates the prior Sunday

WeekStartDate = TDate[Date] -  WEEKDAY(TDate[Date],2)

WeekEndDate = TDate[Date] -  WEEKDAY(TDate[Date],2)+6

[Greg_Deckler]

@ctedesco3307 Well, you would need to use a Sort By column. You could likely use a WEEKNUM column for that.