Week End Date With Text WE

[chethan]

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	WE 14-Mar-15
9824362211	3/6/2015	WE 7-Mar-15
9824362212	3/27/2015	WE 28-Mar-15
9824362213	8/18/2015	WE 22-Aug-15
9824362214	3/17/2015	WE 21-Mar-15
9824362215	5/2/2015	WE 2-May-15
9824362216	8/18/2015	WE 22-Aug-15
9824362217	3/9/2015	WE 14-Mar-15
9824362218	3/11/2015	WE 14-Mar-15
9824362219	2/20/2015	WE 21-Feb-15
9824362220	3/9/2015	WE 14-Mar-15
9824362221	11/20/2015	WE 21-Nov-15
9824362222	2/24/2015	WE 28-Feb-15
9824362223	8/27/2015	WE 29-Aug-15

Regards,

Chethan K

[v-yulgu-msft · Accepted solution]

Hi @chethan,

Pleaser refer to the following steps.

1. Create a calendar table: 

   DateTable = CALENDAR(DATE(2015,1,1),DATE(2015,12,31))

2. In this calendar table, add two calculated columns: 

   WeekDay = WEEKDAY(DateTable[Date],2)

3. SaturdayInDateTable =
       DATEADD ( DateTable[Date], 6 - DateTable[WeekDay], DAY )

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

[chethan]

@v-yulgu-msft Thanks for replay.

please suggest how to sort the data in dashboard by week wise

Regards,

Chethan K

[v-yulgu-msft]

Hi @chethan,

Would you please describe your requirement more clearly? Can you show me the expect output?

Thanks,
Yuliana Gu

[Anonymous]

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)

[chethan]

@Anonymous

its not working

[Anonymous]

Sorry, which bit isn't working?  What result are you geting that is not as expected?

Can you post a screenshot etc.?

Cheers.