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.

     

     

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

I have personally found that instead of dealing with dates in the Query Editor that it is instead preferrable to bash my head repeatedly into a brick wall until I pass out from blood loss and forget that I was trying to attempt something so foolish. But ImkeF might be able to assist.

mcnater's avatar
mcnater
Advocate I
8 years ago

Haha...but if I have a nice Calendar query, I can simply copy it from one report to the next when I create them. Adding in a DAX measure would add 1 more step would it not? Not the end of the world but I'm trying to keep things simple and tidy. Maybe I'm just missing something but that's the goal anyway.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    No, I absolutely get it. I'm just saying that dealing with dates in M code can be incredibly frustrating.

    • mcnater's avatar
      mcnater
      Advocate I
      8 years ago

      I'm definitely starting to feel the same way...

      • mcnater's avatar
        mcnater
        Advocate I
        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.