Forum Discussion

Irussaa's avatar
Irussaa
Helper I
6 years ago
Solved

ASAP - Need formula

Hi guys,   Anyone have formulation to generate weekending data like the picture below (rounded in red line)? I need colum of data that contain week ending date with format just like in the pict at...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Irussaa ,

     

    If you want a DAX formula, you could try the following DAX:

    Column =
"W" & WEEKNUM ( 'DateDim'[WeekEnding], 1 ) & " "
    & MONTH ( 'DateDim'[WeekEnding] ) & "/"
    & DAY ( 'DateDim'[WeekEnding] )

     

Irussaa's avatar
Irussaa
Helper I
6 years ago

It works!  v-eachen-msft 

 

But I have one more question, why is that first week ending in the beginning of the month (which is the first week in january) already generate WE2 not WE1? 

 

 

Thank you

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi Irussaa ,

 

Replace WEEKNUM ( 'DateDim'[WeekEnding], 1 ) with WEEKNUM ( 'DateDim'[WeekEnding], 2 ). After my tests, it will work.

 

  • Irussaa's avatar
    Irussaa
    Helper I
    6 years ago

    v-eachen-msft I'm sorry can I ask one more question?

     

    Why the WE data isn't arranged in the chart?

     

     

    Thank you!

    • PattemManohar's avatar
      PattemManohar
      Community Champion
      6 years ago

      Irussaa Please change your WE column "Sort By Column" to the WeekEndingNumber that needs to be derived from WeekEndingDate. (As you can't directly use the WeekEndingDate for Sort By Column, as WE is using that field)