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
5 years ago

Can you tell me how to correct this range ... someone it's not picking up the actual date range from column 2 

example in screenshot. 2/4/20 displays 12/14-12/20 . if I use .[date] displays 12/28/-12/31 any suggestions?

Week Selected =
VAR __day_selected =
MAXX ( ALLSELECTED ( 'Views'[Day] ), 'Views '[Day] )
VAR __start_of_week =
__day_selected - WEEKDAY ( __day_selected, 2 ) + 1
VAR __week_selected =
DATESINPERIOD ( 'Views'[Day], __start_of_week, 7, DAY )
RETURN
MINX ( __week_selected, 'Views '[Day] ) & " to " & MAXX ( __week_selected, 'Views'[Day] )