Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
prakash11440278
Post Prodigy
Post Prodigy

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

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. 

 

prakash11440278_1-1687515045559.png

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
    )

vyiruanmsft_0-1687763709644.png

Best Regards

View solution in original post

2 REPLIES 2
gojohnson
Frequent Visitor

Hi @Anonymous ! 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?

Anonymous
Not applicable

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
    )

vyiruanmsft_0-1687763709644.png

Best Regards

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors