Friendly Week Names

[lcasey]

Hello,

In Excel it is real easy to create friendly week names such as:

From Jul 9 to Jul 15

From Jul 16 to Jul 22

From Jul 30 to Aug 5

From Aug 6 to Aug 12

From Aug 13 to Aug 19

From Aug 20 to Aug 26

From Aug 27 to Sep 2

From Sep 3 to Sep 9

Is there any way to do this in Power BI from a Date field?

[v-juanli-msft · Accepted solution]

Hi @lcasey

In Queries Editor,

Add custom columns

weeknum=Date.WeekOfYear([date], Day.Monday)

start of week=Date.StartOfWeek([date], Day.Monday)

end of week=Date.EndOfWeek([date], Day.Monday)

Click on column [date] column, select "Add column"->Date, insert "Name of Month"

Click on column [start of week] column, select "Add column"->Date, insert "Day"

Click on column [end of week] column, select "Add column"->Date, insert "Day"

Click on column [Month Name] and [Day], select "Add column"->"Merge columns"  (separator:space)

Click on column [Month Name] and [Day.1], select "Add column"->"Merge columns"  (separator:space)

Add a custom column

Custom=Text.Combine({"From", [Merged],"to",[Merged.1]}, " ")

Then ypu could remove other columns, only keep the last column

Best Regards

Maggie

[lcasey]

This actually does not work...

Look at the October dates.  It should be from October 29th to November 3rd 

[lcasey]

I think I need to add Month Name for week 2 also and then I can use both month names in the query.  That should resolve it.

[lcasey]

Ok all I needed to do was create a month name for Week 2, then Merge Month 2 and week 2 together which fixed it:

[lcasey]

There is an issue with this.

Look at how the dates for October show:

[lcasey]

There is no option to insert column.  

Where do you add colum?  I selected date but there is no insert option.  

[lcasey]

Ok got it---- Here is a picture in case anyone needs :

[lcasey]

Thank you!

This is exactly what I was trying to do!

[HotChilli]

It's done pretty much the same way, usually with a Date table and WEEKNUM function

[lcasey]

True, I do understanbd that, but here is what I get when using weekno function:

1

2

3

4

5

6

7

8

What I need is

Jan 1 - Jan 7

Jan 8 - Jan 14

Jan 15 - Jan 21

Jan 22 - Jan 28

Rather than the numbers.