Week number reset for every month should start on Monday & end on Sunday

[prakash11440278]

Hi PBI Experts,

I have requirement to show the week number which it should reset for every month. The week always should start on Monday and end on Sunday. Below is the April month calendar screen shot for your reference. I am using date dimension in my model where i need to add the week number as per below screen shot logic. Please help. 

[gojohnson]

Hi @v-yiruan-msft ! I am applying the same logic to a report I am building and this solution is working for the first year. However, the subsequent years are not calculating correctly. What needs to be added/changed for it to flow across multiple years?

[v-yiruan-msft · Accepted solution]

Hi @prakash11440278 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Column = EOMONTH('Date'[Date],0)+(7-WEEKDAY(EOMONTH('Date'[Date],0),2))

Week = 
VAR _minwkdate =
    CALCULATE (
        MIN ( 'Date'[Date] ),
        FILTER (
            'Date',
            MONTH ( 'Date'[Date] ) = EARLIER ( 'Date'[Date].[MonthNo] )
                && WEEKDAY ( 'Date'[Date], 2 ) = 1
        )
    )
VAR _minpwkdate =
    CALCULATE (
        MIN ( 'Date'[Date] ),
        FILTER (
            'Date',
            MONTH ( 'Date'[Date] )
                = EARLIER ( 'Date'[Date].[MonthNo] ) - 1
                && WEEKDAY ( 'Date'[Date], 2 ) = 1
        )
    )
VAR _precol =
    CALCULATE (
        MAX ( 'Date'[Column] ),
        FILTER ( 'Date', 'Date'[Column] < EARLIER ( 'Date'[Column] ) )
    )
VAR _maxwkdate =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER (
            'Date',
            MONTH ( 'Date'[Date] ) = EARLIER ( 'Date'[Date].[MonthNo] )
                && WEEKDAY ( 'Date'[Date], 2 ) = 7
        )
    )
VAR _mindate =
    IF ( 'Date'[Date] <= _precol, _minpwkdate, _minwkdate )
VAR _maxdate =
    IF ( 'Date'[Date] <= _precol, _precol, 'Date'[Column] )
RETURN
   IF (
        'Date'[Date] >= _mindate,
       "W"&  ROUNDDOWN ( ( 'Date'[Date] - _mindate ) / 7, 0 ) + 1
    )

Best Regards