cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ctedesco3307
Helper II
Helper II

Concat Date Column to show in correct week order

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!! 

 

ctedesco3307_0-1634836913991.png

ctedesco3307_1-1634836922512.png

 

 

3 ACCEPTED SOLUTIONS

Greg_Deckler_0-1634925220915.png

@ctedesco3307 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

11 REPLIES 11
ctedesco3307
Helper II
Helper II

@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_0-1634925220915.png

@ctedesco3307 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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

ctedesco3307_0-1634925872006.png

 

@ctedesco3307 Did you get an error?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

thx

@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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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

ctedesco3307_0-1634990930590.png

 

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

ctedesco3307_1-1634990969453.png

 

 

@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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@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_0-1635006824362.png

 

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

ctedesco3307_0-1634990930590.png

 

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

ctedesco3307_1-1634990969453.png

 

 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.