Start and end of week with specific date & time

[TechR21]

Hi,

i created 2 columns (week start date, and weeknum) based on a creationdate (format 08/11/2022 10:51:20)

I  start the week on a fri and end on a thursday with the following:

Week Start date(Fri-Thu) = 'Incidents'[created]+-1*if(WEEKDAY(Incidents[created])<6,WEEKDAY('Incidents'[created])+1,WEEKDAY('Incidents'[created])-6  )

With this i created another column to determine the weeknumber:

weeknummmer = WEEKNUM(Incidents[Week Start date(Fri-Thu)])

Now i want the same, but the week should start at fridays, at 17:00:00 and end at the next friday at 16:59:59

How do I include a specific time to determine the start and end of a week?

[Daryl-Lynch-Bzy · Accepted solution]

Hi @TechR21 - DAX does not have a Start of Week function, so this is reason why I suggested using Power Query instead.  If you need to use DAX you could consider the following:

Week Start date(Fri-Thu) = 
VAR _datetime = 'Incidents'[created]
/"Friday will equal 1*/
VAR _day = WEEKDAY( _datetime , 15 ) 
/*This returns the start of week based on Friday
VAR _StartOfWeek = 
      IF ( 
          AND( _day = 1 , HOUR ( _datetime ) >= 17 ), 
          DATE( YEAR( _datetime) , MONTH( _datetime) , DAY( _datetime ) ),
          IF ( AND( day = 1, HOUR ( _datetime ) < 17 ), 
               DATE( YEAR( _datetime) , MONTH( _datetime) , DAY( _datetime ) ) - 7,
               DATE( YEAR( _datetime) , MONTH( _datetime) , DAY( _datetime ) ) - _day + 1
      ) 
RETURN
    _StartOfWeek 

[Daryl-Lynch-Bzy · Accepted solution]

Hi @TechR21 - DAX does not have a Start of Week function, so this is reason why I suggested using Power Query instead.  If you need to use DAX you could consider the following:

Week Start date(Fri-Thu) = 
VAR _datetime = 'Incidents'[created]
/"Friday will equal 1*/
VAR _day = WEEKDAY( _datetime , 15 ) 
/*This returns the start of week based on Friday
VAR _StartOfWeek = 
      IF ( 
          AND( _day = 1 , HOUR ( _datetime ) >= 17 ), 
          DATE( YEAR( _datetime) , MONTH( _datetime) , DAY( _datetime ) ),
          IF ( AND( day = 1, HOUR ( _datetime ) < 17 ), 
               DATE( YEAR( _datetime) , MONTH( _datetime) , DAY( _datetime ) ) - 7,
               DATE( YEAR( _datetime) , MONTH( _datetime) , DAY( _datetime ) ) - _day + 1
      ) 
RETURN
    _StartOfWeek 

[TechR21 · Accepted solution]

Im getting the following error:

[Daryl-Lynch-Bzy]

Sorry I missed an "_" before day on Row 8

[TechR21]

Yes now it working, and exactly the results I expected! Thank you so much!

[Daryl-Lynch-Bzy]

Hi @TechR21 - I would like to recommend using Power Query to add a new column which returns the StartOfWeek.  Something like the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc8xCsAgEETRu1gr7ExMFM+RTrz/NZIyAT9s9Zs3O2dy2EUqilt1RLyXVv73E/oFvUHvuy5wBa7AFbgC1+Ae4Na9a/jX3z3rAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week Beginning", each if 
                    Date.DayOfWeek( [Column1] , Day.Friday ) = 0 
                        and 
                    Time.Hour( Time.From( [Column1] ) ) >= 17 
                        then
                    Date.From( Date.StartOfWeek([Column1] , Day.Friday ) )
                        else 
                    Date.From( Date.StartOfWeek([Column1] , Day.Saturday ) ) - #duration(1, 0, 0, 0)
            , type date)
in
    #"Added Custom"

[TechR21]

thank you, but I really just want to add the time in my previous created column as asked. If not possible I can try other things.