1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by trprasad78, Feb 18, 2019.

  1. trprasad78

    trprasad78 Member

    Messages:
    193
    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
  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    984
  3. trprasad78

    trprasad78 Member

    Messages:
    193
    I am getting below error, can you suggest the correct syntex

    upload_2019-2-19_7-46-55.png
  4. trprasad78

    trprasad78 Member

    Messages:
    193
    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
  5. trprasad78

    trprasad78 Member

    Messages:
    193
    I got it !!!
    did changes as below

    Date.WeekOfMonth(YourDate, Day.Sunday)

    thank you so much, How to do same in Microsoft excel ?
    Thomas Kuriakose likes this.
  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    984
    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.

    Attached Files:

  7. trprasad78

    trprasad78 Member

    Messages:
    193
    Just noticed
    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
  8. trprasad78

    trprasad78 Member

    Messages:
    193
    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
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    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.
  10. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    984
    Hi Chihiro, I'm guessing the point is to calculate this in a dim table. Or this is what you are telling?

    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.
  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    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).
  12. trprasad78

    trprasad78 Member

    Messages:
    193
    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.
    upload_2019-2-20_10-50-23.png

    Attached Files:

  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    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?
  14. trprasad78

    trprasad78 Member

    Messages:
    193
    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.
  15. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Using "M" query language...

    I'd generate calendar table like below (taking 2019 as example).
    Code (vb):
    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.
    Thomas Kuriakose and trprasad78 like this.
  16. trprasad78

    trprasad78 Member

    Messages:
    193
    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 ?
  17. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Change Source line to something like...
    Code (vb):
        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 (vb):
    ="Week" & Number.ToText([AdjustedWkOfMonth],"0")
  18. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    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: Feb 20, 2019
  19. trprasad78

    trprasad78 Member

    Messages:
    193
    thank you i added custom column
  20. trprasad78

    trprasad78 Member

    Messages:
    193
    I need this table from 2011 to 2019
    Next year I have to increase to 2020
    Please suggest me how to do that
  21. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    It would be best if you gave all the conditions up front.
  22. trprasad78

    trprasad78 Member

    Messages:
    193

    sorry for inconvenience , and thank you for your support :)

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

    upload_2019-2-21_18-33-19.png

    [​IMG]
  23. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    I know. I'm reviewing. ;) Read my post #18.
  24. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Ok, I've changed my approach. Went with custom function route.
    Name it fnAdjWkNum
    Code (vb):
    (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 (vb):
    ="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: Feb 21, 2019
    Thomas Kuriakose and trprasad78 like this.
  25. trprasad78

    trprasad78 Member

    Messages:
    193
    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 ?

Share This Page