Forum Discussion

Niraj_vora0106's avatar
Niraj_vora0106
Helper III
4 years ago
Solved

Week Start calculation

Hi community, 

 

I want to group dates between Week_Start and Week_End.

Yes I tried using WEEKDAY() function but I want it to be Dynamic. 

 

For eg: 

The current week in India, if I take Monday as starting is from 4th July - 10th July 2022.

 

So after 10th July as the date changes to 11th July, and so will the week.

I want the week to change dynamically ie : from 11th July - 17th July 2022.

 

Is there any way we can do this?

 

Please Help!

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Niraj_vora0106 , You can get week start and week end like

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

     

     

    week name = format( 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 , "dd \t\h mmmm - ") &
     format( 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2), "dd \t\h mmmm yyyy") 

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Niraj_vora0106 , You can get week start and week end like

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

     

     

    week name = format( 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 , "dd \t\h mmmm - ") &
     format( 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2), "dd \t\h mmmm yyyy") 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Niraj_vora0106 ,

    Please refer to my pbix file to see if it helps you.

    Create two columns.

    weekday = WEEKDAY('Table'[Date],2)
    weeknum = WEEKNUM('Table'[Date],2)

    Then create a measure.

    Measure =
VAR _startday =
    WEEKDAY ( TODAY (), 2 )
VAR _startweeknum =
    WEEKNUM ( TODAY (), 2 )
RETURN
    IF (
        MAX ( 'Table'[weeknum] ) = _startweeknum
            && MAX ( 'Table'[weekday] ) >= 1,
        MAX ( 'Table'[Date] ),
        IF (
            MAX ( 'Table'[weeknum] ) = _startweeknum
                && MAX ( 'Table'[weekday] ) <= 7,
            MAX ( 'Table'[Date] ),
            BLANK ()
        )
    )

     

     If I have misunderstood your meaning, please provide more details with your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.