Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Syndicate_Admin
Administrator
Administrator

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 2
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1626940766680.png

 

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

vluwangmsft_1-1626940800888.png

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

vluwangmsft_2-1626940990513.png

 

Wish it is helpful for you!

 

 

Best Regards

Lucien

amitchandak
Super User
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])))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.