• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get week from date - Power BI desktop

trprasad78

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
 

trprasad78

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
 

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

trprasad78

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.

Please check below screenshot

upload_2019-2-19_13-4-11.png
 

trprasad78

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

upload_2019-2-19_13-10-41.png
 

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

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?
 

trprasad78

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"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "FirstSunOfMonth", each Date.AddDays(Date.AddDays(#date([Year],[Month],1),-1),{7,6,5,4,3,2,1}{Date.DayOfWeek(Date.AddDays(#date([Year],[Month],1),-1))})),
    #"Added Custom5" = Table.AddColumn(#"Added Custom2", "FirstSunNextMonth", each Date.AddDays(Date.AddDays(Date.AddMonths(#date([Year],[Month],1),1),-1),{7,6,5,4,3,2,1}{Date.DayOfWeek(Date.AddDays(Date.AddMonths(#date([Year],[Month],1),1),-1))})),
    #"Added Custom3" = Table.AddColumn(#"Added Custom5", "AdjustedWkOfMonth", each if [FirstSunOfMonth] > [Date] and Date.Day([FirstSunOfMonth]) < 4 then Date.WeekOfMonth(Date.AddDays([FirstSunOfMonth],-7)) else if [Date]>=Date.AddDays([FirstSunNextMonth],-7) and Duration.Days(Date.EndOfMonth([Date])-Date.AddDays([FirstSunNextMonth],-7))>=4 then Date.WeekOfMonth(Date.AddDays([FirstSunNextMonth],-7)) else if [Date]=Date.EndOfMonth([Date]) then 1 else Date.WeekOfMonth([Date]))
in
    #"Added Custom3"
You can then join this to your fact table... or just load this as dim table and build relationship based on Date column.
 

trprasad78

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"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "FirstSunOfMonth", each Date.AddDays(Date.AddDays(#date([Year],[Month],1),-1),{7,6,5,4,3,2,1}{Date.DayOfWeek(Date.AddDays(#date([Year],[Month],1),-1))})),
    #"Added Custom5" = Table.AddColumn(#"Added Custom2", "FirstSunNextMonth", each Date.AddDays(Date.AddDays(Date.AddMonths(#date([Year],[Month],1),1),-1),{7,6,5,4,3,2,1}{Date.DayOfWeek(Date.AddDays(Date.AddMonths(#date([Year],[Month],1),1),-1))})),
    #"Added Custom3" = Table.AddColumn(#"Added Custom5", "AdjustedWkOfMonth", each if [FirstSunOfMonth] > [Date] and Date.Day([FirstSunOfMonth]) < 4 then Date.WeekOfMonth(Date.AddDays([FirstSunOfMonth],-7)) else if [Date]>=Date.AddDays([FirstSunNextMonth],-7) and Duration.Days(Date.EndOfMonth([Date])-Date.AddDays([FirstSunNextMonth],-7))>=4 then Date.WeekOfMonth(Date.AddDays([FirstSunNextMonth],-7)) else if [Date]=Date.EndOfMonth([Date]) then 1 else Date.WeekOfMonth([Date]))
in
    #"Added Custom3"
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. :oops: I'll have to revisit this one. My eyes are getting crossed looking at the table :p
 
Last edited:

trprasad78

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
 

trprasad78

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. :oops: I'll have to revisit this one. My eyes are getting crossed looking at the table :p
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
Ok, I've changed my approach. Went with custom function route.
Name it fnAdjWkNum
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:
=fnAdjWkNum([Date column name])

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:

trprasad78

Member
Ok, I've changed my approach. Went with custom function route.
Name it fnAdjWkNum
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:
=fnAdjWkNum([Date column name])

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.
thanks lot :) its working great :)

where can i learn this language ? i want to expert in this.
same kind of function can create with excel table ?
 
Top