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-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Syndicate_Admin 

     

    Yes, you can create a measure ,and use it in filter pane to filter data. Also, you can modify your 'current year' measure and only calculate the first 20 weeks. But my question is why it is 20 weeks and what is the logic, because now July is more than 20 weeks. If you can't solve your problem, please share more information and your measure then we can help you soon.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.