Get week from date - Power BI desktop

Member
Hi all,

I need your support to get week from demo completed date

Week start on Sunday end on Saturday

Example will take Feb 2019 calendar

1st Feb and 2nd Feb date following on Jan month.

In a week maximum day following on which ever month we need consider as that month.

Minimum week is 4 maximum week 5

Jan 2019 Month have 5 weeks - from 30th Dec to 2nd Feb

Feb 2019 month have 4 weeks - 3rd Feb to 2nd Marach

Mar 2019 Month have 4 weeks - 3rd March to 30th March

Please suggest how to get calculated week column as per above scenario

01-Feb-19 Week - 5
31-Jan-19 Week - 5
04-Feb-19 Week - 1
01-Feb-19 Week - 5
08-Feb-19 Week - 1
31-Jan-19 Week - 5
01-Feb-19 Week - 5
01-Feb-19 Week - 5
13-Feb-19 Week - 2
31-Jan-19 Week - 5
18-Feb-19 Week - 3
13-Feb-19 Week - 2

Member
I am getting below error, can you suggest the correct syntex

Member
Perhaps Date.WeekOfYear via Power Query?

Date.WeekOfYear(YourDate, Day.Sunday), the second argument is the first day of week.

This might also bring some light in the dark.
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13507155-iso-week-number-option-on-date-weekofyear-function
this working in Edit query mode .

But its giving 52 weeks in a year, what i want is maximum week should be 5 weeks
week as to end in same month, next month start from week 1

what is the best way please sugguest

Member
I got it !!!
did changes as below

Date.WeekOfMonth(YourDate, Day.Sunday)

thank you so much, How to do same in Microsoft excel ?

GraH - Guido

Well-Known Member
Hi, I had some trouble understanding what you require first. Glad you found the correct M-function youreself.

Concerning Excel alternative, you might want to check this out.
Formula can be combined into a single one, but it was easier to show the build up in different columns.
I have not checked all possibilities, so be careful.

Attachments

• 9.4 KB Views: 3

Member
Just noticed
I got it !!!
did changes as below

Date.WeekOfMonth(YourDate, Day.Sunday)

thank you so much, How to do same in Microsoft excel ?
Just noticed its not working as i expecting

In a week maximum day following on which ever month we need consider as that month week.

Member
Hi, I had some trouble understanding what you require first. Glad you found the correct M-function youreself.

Concerning Excel alternative, you might want to check this out.
Formula can be combined into a single one, but it was easier to show the build up in different columns.
I have not checked all possibilities, so be careful.
Thank you for sharing excel... it working good. But if i put 1st of March 2019 i am getting week 9

Chihiro

Excel Ninja
This sort of calculation is best done in dimension table and joined back (or create one to many relationship) to fact table.

However, how do you intend to deal with when year changes?
Ex:
1/1/2019 belongs to week that starts on 12/30/2018. Should it be Week1? Or Week5?

If former, 1/31/2019 = Week4.

GraH - Guido

Well-Known Member
Hi Chihiro, I'm guessing the point is to calculate this in a dim table. Or this is what you are telling?

Thank you for sharing excel... it working good. But if i put 1st of March 2019 i am getting week 9
Like I said, I did not have the time this morning to check all possibilities. I think I'm starting to see what you are after.

Chihiro

Excel Ninja
I'm guessing the point is to calculate this in a dim table.
Correct. This should not be calculated in fact table. But in dimension/dim table.

And, rather than doing it in DAX, should be done at query stage or using "M". As, this info does not require frequent refresh/recalc (once loaded, really doesn't change based on filter or any other operation on the tables).

Member
Please refer following image. hope it will be clear.

If i use Date.WeekOfMonth(YourDate, Day.Sunday) formula
1st feb and 2nd feb showing as Week1 of Feb ,

actually what we expecting as mentioned below.

Attachments

• 32.8 KB Views: 4

Chihiro

Excel Ninja
What is the logic?
What makes week of Dec 30th week 1? Is it because more dates fall in January?
Would same logic apply for other months?

Member
What is the logic?
What makes week of Dec 30th week 1? Is it because more dates fall in January?
Would same logic apply for other months?
yes correct, if More dates following on that week consider as that month week.
In 7 days, 4 days fall on which month based on that week number has to consider.

Chihiro

Excel Ninja
Using "M" query language...

I'd generate calendar table like below (taking 2019 as example).
Code:
``````let
Source = List.Dates(#date(2019,1,1),365,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
in
You can then join this to your fact table... or just load this as dim table and build relationship based on Date column.

Member
Using "M" query language...

I'd generate calendar table like below (taking 2019 as example).
Code:
``````let
Source = List.Dates(#date(2019,1,1),365,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
in
You can then join this to your fact table... or just load this as dim table and build relationship based on Date column.
Thank you so much for your support, which part i have to modify to increase one more year.
also let me know add year 2018.
Instead 1,2,3 we show Week1,Week2 etc ?

Chihiro

Excel Ninja
Change Source line to something like...
Code:
``    Source = List.Dates(#date(2018,1,1),Duration.Days(#date(2020,1,1)-#date(2018,1,1)),#duration(1,0,0,0)),``
Or convert it to custom function and supply Start Date and End Date as argument.

Just add another custom column and use following to display "Week"+#
Code:
``="Week" & Number.ToText([AdjustedWkOfMonth],"0")``

Chihiro

Excel Ninja
Woops, I noticed mistakes in both FirstSun calculation. That will throw things off. I'll post fixed "M" a bit later.

EDIT: Upon closer inspection FirstSun was fine. But I have logic error in AdjustedWkOfMonth. I'll have to revisit this one. My eyes are getting crossed looking at the table

Last edited:

Member
Change Source line to something like...
Code:
``    Source = List.Dates(#date(2018,1,1),Duration.Days(#date(2020,1,1)-#date(2018,1,1)),#duration(1,0,0,0)),``
Or convert it to custom function and supply Start Date and End Date as argument.

Just add another custom column and use following to display "Week"+#
Code:
``="Week" & Number.ToText([AdjustedWkOfMonth],"0")``
thank you i added custom column

Member
Woops, I noticed mistakes in both FirstSun calculation. That will throw things off. I'll post fixed "M" a bit later.

EDIT: Upon closer inspection FirstSun was fine. But I have logic error in AdjustedWkOfMonth. I'll have to revisit this one. My eyes are getting crossed looking at the table
I need this table from 2011 to 2019
Next year I have to increase to 2020
Please suggest me how to do that

Chihiro

Excel Ninja
It would be best if you gave all the conditions up front.

Member
It would be best if you gave all the conditions up front.

sorry for inconvenience , and thank you for your support

below is the tabel as per your query, still not getting what i am expecting.

Chihiro

Excel Ninja
I know. I'm reviewing. Read my post #18.

Chihiro

Excel Ninja
Ok, I've changed my approach. Went with custom function route.
Code:
``````(cDate as date)=>
let
sDate = #date(Date.Year(cDate), 1, 1),
dList = List.Select(List.Dates(sDate, Duration.Days(#date(Date.Year(cDate)+2,1,1) - sDate) + 1, #duration(1,0,0,0)), each Date.DayOfWeek(_, Day.Sunday)=0 and Date.Day(_)<=7),
sList = List.Generate(()=>0, each _ < List.Count(dList), each _ + 1, each if Date.Day(dList{_}) <=4 then dList{_} else Date.AddDays(dList{_},-7)),
mStart = List.Max(List.Select(sList, each _<= cDate)),
mEnd = List.Min(List.Select(sList, each _> cDate)),
mList = List.Dates(mStart, Duration.Days(mEnd - mStart), #duration(1,0,0,0)),
wList = Record.Field(List.Last(List.Generate(
()=> [Date=null, WkNum=null, y=0, x=-1],
each [x] < List.Count(mList) and ([Date]=null or [Date]<=cDate),
each [Date=mList{x}, WkNum=Number.RoundUp((x+1)/7), y=[y]+1, x=[x]+1],
each [[Date],[WkNum],[y]])), "WkNum")
in
wList``````
In your table with date column (make sure it is in Date data type). Then add custom column and invoke custom function.

Ex:

This will return week number. So you just have to concatenate it with "Week".
Ex:
Code:
``="Week"&Number.ToText(fnAdjWkNum([Date column name]),"0")``
EDIT: You could simplify the wList part a bit. But I purposefully left additional info in returned list. So, if something goes wrong, you can change Record.Field to obtain more info on the record returned to help in trouble shooting.

Last edited:

Member
Ok, I've changed my approach. Went with custom function route.
Code:
``````(cDate as date)=>
let
sDate = #date(Date.Year(cDate), 1, 1),
dList = List.Select(List.Dates(sDate, Duration.Days(#date(Date.Year(cDate)+2,1,1) - sDate) + 1, #duration(1,0,0,0)), each Date.DayOfWeek(_, Day.Sunday)=0 and Date.Day(_)<=7),
sList = List.Generate(()=>0, each _ < List.Count(dList), each _ + 1, each if Date.Day(dList{_}) <=4 then dList{_} else Date.AddDays(dList{_},-7)),
mStart = List.Max(List.Select(sList, each _<= cDate)),
mEnd = List.Min(List.Select(sList, each _> cDate)),
mList = List.Dates(mStart, Duration.Days(mEnd - mStart), #duration(1,0,0,0)),
wList = Record.Field(List.Last(List.Generate(
()=> [Date=null, WkNum=null, y=0, x=-1],
each [x] < List.Count(mList) and ([Date]=null or [Date]<=cDate),
each [Date=mList{x}, WkNum=Number.RoundUp((x+1)/7), y=[y]+1, x=[x]+1],
each [[Date],[WkNum],[y]])), "WkNum")
in
wList``````
In your table with date column (make sure it is in Date data type). Then add custom column and invoke custom function.

Ex:
``="Week"&Number.ToText(fnAdjWkNum([Date column name]),"0")``