Splitting Date information into weeks

[Anonymous]

Hello,

I'm having an issue with splitting date time into weeks. I'm unsure of how to accomplish this given the fact that I am using a live dataset connection. I guess the underlying question is how to use WEEKNUM within the context of a live dataset connection. I'm trying to create a graph broken down into weeks with the ability to change months. 

Here's essentially what were looking for:

[AilleryO]

Hi,

Could you add the calculation in your query, M language Date.WeekOfYear()  instead of using DAX WEEKNUM() ?

In case you need ISO Weeknum, you can use this M code :

//Calculation of ISO Weeknumber in 4 steps
//1. Calculate the Thursday of the week 
InsertCurrThursday = Table.AddColumn(InsertMonthEnding, "CurrThursday", each Date.AddDays([DateRef], -Date.DayOfWeek([DateRef],1) + 3), type date),
//2. Calculate 1st of january (cf step 1) 
InsertFirstJan = Table.AddColumn(InsertCurrThursday, "FirstJan", each
#date(Date.Year([CurrThursday]),1,1),type date),
//3. Calculate number of days between 1st january and thursday 
InsertDuration= Table.AddColumn(InsertFirstJan, "Duration", each Duration.Days(Duration.From([CurrThursday] - [FirstJan])), type number),
//4. Divide the calculated number of days at step 3 by 7, round down and add 1  
InsertISOWeekNum = Table.AddColumn(InsertDuration, "NumWeekISO", each Number.RoundDown([Duration]/7)+1), ChangeType5=Table.TransformColumnTypes(InsertISOWeekNum,{{"NumSemISO", Int64.Type}}),
//Remove unusefull columns 
RemovedColumns = Table.RemoveColumns(ChangeType5, {"CurrThursday","FirstJan","Duration"})

Hope this help,

Olivier

[Anonymous]

Unfortunately I dont have any powerquery/ query editing ability. I appreciate the help though!

[Anonymous]

I also should mention that I have no ability to manipulate the data at the database level. I can only use the functionality limited to live dataset connections. 

[v-diye-msft · Accepted solution]

Hi @Anonymous 

For the live-connection mode, you can add the weeknum using the measure below:

Measure 2 = WEEKNUM(MAX(Table1[Date]),2)

 

[amitchandak]

In case you are using a direct query. Then preferably have your date calendar in the database and have weeks define in the database. In case you using the data import, then create a date calendar table wen define week with help of week no

Week = "Week " & weeknum(date[date])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin