Forum Discussion

chethan's avatar
chethan
Resolver 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 Number Opened Date Week - Open 9824362210 3/11/2015 ...
  • 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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft 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
    Resolver 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
      Microsoft Employee
      9 years ago

      Hi chethan,

       

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

       

      Thanks,
      Yuliana Gu