Same Week Numbers in Two Months

[hamzashafiq]

Hi All,

I have created a Week Number column using the following DAX
Week Num= WEEKNUM('DateTable'[Date],2) to calculate the 4 week running average. The problem is that the Week 6 is coming in both Januray and February causing issues in my rolling average calculation. Is there any way we can make the week number unique?

[v-yiruan-msft · Accepted solution]

Hi @hamzashafiq ,

Please create the measures as below to get the 4 weeks rolling average, you can find the details in the attachment.

Measure = SUM('Table'[Billed Amount])

4 Week Rolling Average = 
VAR _selweek =
    SELECTEDVALUE ( 'DateTable'[Week Num] )
VAR _count =
    IF ( _selweek <= 3, _selweek, 4 )
RETURN
    DIVIDE (
        SUMX (
            FILTER (
                ALLSELECTED ( 'DateTable' ),
                'DateTable'[Week Num] >= _selweek - 3
                    && 'DateTable'[Week Num] <= _selweek
            ),
            [Measure]
        ),
        _count
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@hamzashafiq , You can have month week in one two way in your date table

WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1

min week start of month = minx(filter('Date',[Month Year] =earlier([Month Year])),[Week Start date])
week of month = datediff([min week start of month],[date],Week)+1

also have month rank

Month Rank = RANKX(all('Date'),'Date'[Month Start Date],,ASC,Dense)

This Month Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && 'Date'[week of month]=max('Date'[week of month]) ))
Last Month Same Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && 'Date'[week of month]=max('Date'[week of month]) ))