Forum Discussion

mcnater's avatar
mcnater
Advocate I
8 years ago
Solved

Week Start calculation

I'd love to be able to group my dates into Week Start buckets. In Qlikview there is a WeekStart() function where you provider a date and it groups dates into weeks, depending on the date and offset y...
Need Help
  • mcnater's avatar
    mcnater
    8 years ago

    Ok...so this appears to work and do what I need:

     

    WeekStart

    = Table.AddColumn(#"WeekStart", each Date.ToText(Date.StartOfWeek([Date], Day.Sunday),"MM/dd/yyyy"))

     

    WeekStart_WeekEnd

    = Table.AddColumn(#"WeekStart_WeekEnd", each Date.ToText(Date.StartOfWeek([Date], Day.Sunday),"MM/dd/yyyy") & "-" & Date.ToText(Date.EndOfWeek([Date], Day.Sunday),"MM/dd/yyyy"))

     

     

     

    I'll leave this here so the next person trying this can hopefully find it and not bang their head for a full day.


    I appreciate all the help.

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi

 

The below Dax will help to get the week start date. I am assuming that Sunday is your week start date.

 

WeekStartDate = Sheet2[Date]-  WEEKDAY(Sheet2[Date],1)+1  // Sunday as Week start date

WeekStartDate = Sheet2[Date]-  WEEKDAY(Sheet2[Date],2)+1 // Monday as Week start date

 

 

 

 

 

Hope this is what you are looking for.

 

Thansk
Raj

 

mcnater's avatar
mcnater
Advocate I
8 years ago

Is there a way to do it in the Query Editor? I'm using the "Ultimate Calendar" query load and am wondering if can just be added there instead of DAX so all the Date Logic is on the calendar. We are just looking for a Sunday start yes. Is that possible do you know?