cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## 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.
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

.............................................

2 REPLIES 2
Community Support

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

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors