Forum Discussion

hamzashafiq's avatar
hamzashafiq
Icon for Kudo Collector rankKudo Collector
3 years ago
Solved

Same Week Numbers in Two Months

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?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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]) ))

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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