Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

Rolling 4 weeks calculation

Hello
I am calculating Rolling 4 weeks based on data below
W1-W1
W2-W1+W2
W3-W1+W2+W3
W4-W1+W2+W3+W4
W5-W2+W3+W4+W5 (starting from W2)

.....
W1 is 01-January-2020 to 07-January-2020
W2 is 08-Jan-2020 to 14-Jan-2020.


Now my problem is that I have data up to week20 in my current year.
But I'm getting data for Week21[18+19+20] ,Week22[19+20], Week23[20] as well.
Please help me to show only the data up to the actual week.
I have created a table like the following

Weeknum

Weeks

1

1

2

1

2

2

3

1

3

2

3

3

4

1

4

2

4

3

4

4

5

2

5

3

5

4

5

5

6

3

6

4

.............................................
Please find the image below
R4.PNG


2 Replies

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

    Syndicate_Admin , With help from separate week table or Year week /Date and week rank

     

    Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week]>=max('Date'[Week])-4 && 'Date'[Week]<=max('Date'[Week])))

     

     

    With Week year

     

    new column in week year/date table 

    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    measures
    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

    Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Syndicate_Admin ,

    See the below :

    You data from 2020/1/1 to 2020/12/31,and week 1 from 2020/1/1 and end 2020/1/7

    Try like following:

    Table = CALENDAR("2020,1,1","2020,12,31")
    weeknum = IF(YEAR('Table'[Date]-2)<2020,1, WEEKNUM('Table'[Date]-2,2))

    get the below:

     

    Due to without your detail data,I create a sample data:

    Then  4 weeks rolls:

    when week <=4 , get the sum that week < the actual number of weeks.

    when week >4, get the sum that week >=actual number of weeks-3 and week <=actual number of weeks

    roll4week = 
IF (
    MAX ( 'Table'[weeknum] ) <= 4,
    CALCULATE (
        SUM ( 'Table'[number] ),FILTER(ALL('Table'),
       'Table'[weeknum] <= max('Table'[weeknum] ))
    ),
    CALCULATE (
        SUM ( 'Table'[number] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[weeknum]
                >= MAX ( 'Table'[weeknum] ) - 3
                && 'Table'[weeknum] <= MAX ( 'Table'[weeknum] )
        )
    )
)

     

    Wish it is helpful for you!

     

     

    Best Regards

    Lucien