Forum Discussion
Solved
Week End Date With Text WE
Hi, I need Weekend date Satuarday With Text by Adding "WE" below is the example. please help Thanks in Advance. Order Number Opened Date Week - Open 9824362210 3/11/2015 ...
Hi chethan,
Pleaser refer to the following steps.
- Create a calendar table:
DateTable = CALENDAR(DATE(2015,1,1),DATE(2015,12,31))
- In this calendar table, add two calculated columns:
WeekDay = WEEKDAY(DateTable[Date],2)
SaturdayInDateTable = DATEADD ( DateTable[Date], 6 - DateTable[WeekDay], DAY )- In your source table, add two calculated columns:
Saturday = LOOKUPVALUE ( DateTable[SaturdayInDateTable], DateTable[Date], 'WEEK-date'[Opened Date] ) Week-Open = "WE " & 'WEEK-date'[Saturday].[Day] & "-" & LEFT ( 'WEEK-date'[Saturday].[Month], 3 ) & "-" & RIGHT ( 'WEEK-date'[Saturday].[Year], 2 )
If you still have any question, please feel free to ask.
Thanks,
Yuliana Gu- Create a calendar table:
Do you have a Date/Calendar dimension table with a Week Ending column (e.g. http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/)that is linked to your data table?
If so, you can create "Week - Open" as a new column:
Week - Open = "WE " & FORMAT(RELATED(DimDate[Week Ending]), "dd-MMM-yyyy")
Note that I had problems with the "Week Ending" column in my DimDate table falling on a Sunday until I specifically set the start of week to Sunday per below (even though that's the default, and my locale is set OK):
WeekEnding = Table.AddColumn(#"YOUR PREVIOUS STEP", "Week Ending",
each Date.EndOfWeek([Date], Day.Sunday), type date)
Anonymous
its not working
Sorry, which bit isn't working? What result are you geting that is not as expected?
Can you post a screenshot etc.?
Cheers.