Rolling 4 weeks calculation

[Syndicate_Admin]

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

[v-luwang-msft]

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

[amitchandak]

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