Forum Discussion

Harrisfil's avatar
Harrisfil
Icon for Helper I rankHelper I
1 year ago
Solved

Overlapping Weeks Between Months in Calendar Logic

Hello, I’m working on creating a custom week column in my calendar dataset and have encountered a challenge with weeks that span across months. Here’s a detailed explanation of the issue: The Issue...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Harrisfil ,

     

    I changed the arithmetic logic and you can check the new attachment.

    New WeekStart = IF(MONTH('DimDate'[WeekStart])<>MONTH([Date]),[WeekStart]+7,[WeekStart])

New Weeknum = WEEKNUM([New WeekStart],1)

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Harrisfil ,

 

I made simple samples and you can check the results below:

DimDate = 
ADDCOLUMNS (
    'Table',
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "DayName", FORMAT ( [Date], "DDDD" ),
    "WeekDay", WEEKDAY ( [Date],1 ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 1 ) + 1,
    "WeekEnd",   [Date] - WEEKDAY([Date], 1) + 7
)


CorrectedWK = IF([WeekDay] in {5,6,7}&&MONTH([Date])>MONTH([WeekStart]),[Weeknum] +1 ,[Weeknum])

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

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

Harrisfil's avatar
Harrisfil
Icon for Helper I rankHelper I
1 year ago

Thank you for your answer .
i tried it in a full calendar and it is not giving the desired result for allo months across years. For example 31/3/2024 -01/4/2024  it gives same week.

 

Thanks again for spending time on that 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Harrisfil ,

     

    I changed the arithmetic logic and you can check the new attachment.

    New WeekStart = IF(MONTH('DimDate'[WeekStart])<>MONTH([Date]),[WeekStart]+7,[WeekStart])

New Weeknum = WEEKNUM([New WeekStart],1)

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

    • Harrisfil's avatar
      Harrisfil
      Icon for Helper I rankHelper I
      1 year ago

      Thank you very much for your effort and your time