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

 

Thank you.

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] )

     

10 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Irussaa 

     

    ColumnName = 

Text.Combine({[Week]," ",Number.ToText([MonthN]),"/", Number.ToText(Date.Day 
 (Date.EndOfWeek([date]))})

     

    Tell me if this works, since I did not have your table, I did this free hand. This is in Power Query.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel 

    • Irussaa's avatar
      Irussaa
      Helper I
      6 years ago

      Hi Nathaniel_C 

       

      I try to apply 

      ColumnName = 

Text.Combine({[Week]," ",Number.ToText([MonthN]),"/", Number.ToText(Date.Day 
 (Date.EndOfWeek([date]))})

       

      Inside my data set:

       

      WE = Text.Combine({[DayofWeek]," ",Number.ToText([MonthofYear]),"/", Number.ToText(DateDim[Day]
      (Date.EndOfWeek([Date]))})
       
       
      But its Error.
       
      Can you explain to me what is 
      [Week]
      [MonthN]
      Date.Day
      (Date.EndOfWeek([date])
      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Hello Irussaa ,
        Here is a link to Power Query Power Query . It is the best place to add Calculated Columns.

        However this is a way that you can do it in Power BI. 

        WE= table[Week] && " " && table[MonthN] && "/" && Day(table[Date]+ 7-WEEKDAY(table[Date],1))

        In your post you show a table with columns. I took the name of the columns from that picture. You will want to replace "table" in my formula with your table name. 

         

         
        Let me know if you have any questions.

        If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
        Nathaniel

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    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.