Forum Discussion

chethan's avatar
chethan
Icon for Resolver III rankResolver III
9 years ago
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 NumberOpened DateWeek - Open
98243622103/11/2015WE 14-Mar-15
98243622113/6/2015WE 7-Mar-15
98243622123/27/2015WE 28-Mar-15
98243622138/18/2015WE 22-Aug-15
98243622143/17/2015WE 21-Mar-15
98243622155/2/2015WE 2-May-15
98243622168/18/2015WE 22-Aug-15
98243622173/9/2015WE 14-Mar-15
98243622183/11/2015WE 14-Mar-15
98243622192/20/2015WE 21-Feb-15
98243622203/9/2015WE 14-Mar-15
982436222111/20/2015WE 21-Nov-15
98243622222/24/2015WE 28-Feb-15
98243622238/27/2015

WE 29-Aug-15

 

 

 

Regards,

Chethan K

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
        Anonymous
        Not applicable
        9 years ago

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

         

        Can you post a screenshot etc.?

         

        Cheers.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      chethan
      Icon for Resolver III rankResolver III
      9 years ago

      v-yulgu-msft Thanks for replay.

       

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

       

       

      Regards,

      Chethan K

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi chethan,

         

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

         

        Thanks,
        Yuliana Gu