Weeks into Month by Number of Days

[mb0307]

Hi,

In my Date table, I want to convert weeks into monthly buckets by using this logic: 

If a week falls between two months, I will assign that week to the month with the most number of days for that week. 

Week Format:  WEEK/YEAR  (03/2024)

Example below,

• Week 5 should be assigned to Feb 2024 (4 days in Feb)
• Week 9 should be assigned to Feb 2024 ( 4 days in Feb)

How can I achieve this in Calculated Column in Date Table please?

Thanks 

[Greg_Deckler]

@mb0307 Maybe:

Week = 
    VAR __Date = [Date]
    VAR __Year = YEAR([Date])
    VAR __Week = WEEKNUM( __Date, 2 )
    VAR __Month = MONTH( __Date )
    VAR __Table = 
        ADDCOLUMNS(
            FILTER( 'Dates', WEEKNUM( [Date] ) = __Week ),
            "__Month", MONTH( [Date] )
        )
    VAR __Min = MINX( __Table, [__Month] )
    VAR __DaysInMonth = COUNTROWS( FILTER( __Table, [__Month] = __Month ) )
    VAR __Result = 
        SWITCH( TRUE(),
            __DaysInMonth > 3, FORMAT( __Week, "00" ) & "/" & __Year,
            __Min = __Month, FORMAT( __Week + 1, "00") & "/" & __Year,
            FORMAT( __Week - 1, "00" ) & "/" & __Year
        )
RETURN
    __Result