Get week from date - Power BI desktop

[Chihiro]

where can i learn this language ?

I learnt it through trial and error mostly. And liberal use of Google search. I use forum(s) to find interesting problems and then set out to solve it to advance my skill.

I don't personally use books to learn things... but I hear good things about below.
https://www.amazon.ca/Data-Monkey-Guide-Language-Excel/dp/1615470344

 

[trprasad78]

I learnt it through trial and error mostly. And liberal use of Google search. I use forum(s) to find interesting problems and then set out to solve it to advance my skill.

I don't personally use books to learn things... but I hear good things about below.
https://www.amazon.ca/Data-Monkey-Guide-Language-Excel/dp/1615470344

i have to thank Chandoo.org to create forum to get associate person like you thank you soo much

 

[trprasad78]

Ok, I've changed my approach. Went with custom function route.
Name it fnAdjWkNum

(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:

="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.

For get this in excel or vba ? instead of maintain Week table we can use formula or function ? please suggest.
Sorry not opening new thread for this..

 

[Chihiro]

You don't need to maintain week table with custom function approach.
Just add custom column to your table and use date column as argument in the function.

NOTE: However, for any sort of analysis involving time intelligence, it is recommended that you have date/calendar dimension table. Many of time intelligence function in DAX depends on it (ParallelPeriod etc).