Sum of Next 3 Weeks

pociners

New Member
Joined
Mar 19, 2014
Messages
32
Hi Everyone,

I wanna ask something, can we sum the next 2 weeks including the current week?
This is the data
16hw4gk.jpg


This is the result that i want
21343g7.jpg


Can you help me how to calculate that in powerpivot?

thanks and best regards :)
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Yes. To do it you need some set up.

First make sure your data table uniquely identifies each week. So the yearweek column in your pivot is good. Every row in your data table has the unique week ID 201501 for example.
Then create a calendar table that also has these unique week IDs. join the data table on this column.
Your calendar table should contain an ID column (in addition to the week ID). Make it start at 1 for the first week in your calendar, 2 for the second week etc right up until the last week in your calendar table. You will use this ID column to help do the calculation.

Then write the following Measure (change the table and column names to match your tables).

Total Sales Current + 2 Weeks := Calculate(sum(Data[Value]),filter (All(Calendar),calendar[ID] >= min(Calendar[ID]) && Calendar[ID] <= min(Calendar[ID])+2))

make sure you use columns from your calendar table in your pivot table Rows (don't use date columns from your data table).
 
Last edited:
Upvote 0
Yes. To do it you need some set up.

First make sure your data table uniquely identifies each week. So the yearweek column in your pivot is good. Every row in your data table has the unique week ID 201501 for example.
Then create a calendar table that also has these unique week IDs. join the data table on this column.
Your calendar table should contain an ID column (in addition to the week ID). Make it start at 1 for the first week in your calendar, 2 for the second week etc right up until the last week in your calendar table. You will use this ID column to help do the calculation.

Then write the following Measure (change the table and column names to match your tables).

Total Sales Current + 2 Weeks := Calculate(sum(Data[Value]),filter (All(Calendar),calendar[ID] >= min(Calendar[ID]) && Calendar[ID] <= min(Calendar[ID])+2))

make sure you use columns from your calendar table in your pivot table Rows (don't use date columns from your data table).

Nice, Your formula works very well!!
but i still dont understand about this logic
Code:
[COLOR=#333333]min(Calendar[ID]) && Calendar[ID] <= min(Calendar[ID])+2[/COLOR]
yesterday i asked about running total in this forum and Ozeroth told me to use this logic
Code:
[COLOR=#333333]Table1[YearWeek] <= MAX ( Table1[YearWeek][/COLOR]
can you explain it to me what is the difference?
This Min and Max function little bit confuse me
 
Upvote 0
Yes I realise I was light on explanation. I'm actually glad you asked. - that shows genuine curiosity about "why".

This is actually a trick. You need to forget about max and min, and start to think about filter context.


Here is my formula.

Code:
Total Sales Current + 2 Weeks := 
     Calculate(sum(Data[Value]),
           filter (All(Calendar),
                calendar[ID] >= min(Calendar[ID]) && 
                Calendar[ID] <= min(Calendar[ID])+2
          )
     )

so starting with the filter portion.


First of all, ALL(calendar) removes all filters from the current filter context. What ever filters are in the pivot table on the calendar - forget them - they are gone.

Then the next 2 lines reapply some new filters. Min(calendar[ID]) "harvests" the minimum vaule from the current filter context - in other words it looks at the pivot table and checks what filters are currently applied. In effect it "looks" at your pivot table and works out the minimum value of calendar ID for each cell in the pivot table. Now in the pivot table you built, each row has only 1 week. Each week has only one ID, right? So what is the id for (say) 201501? Well, it is a number. Let's say 7. So what is the min of 7? What is the max of 7? What is the sum of 7? What is the average of 7? The answer is always the same. The answer is 7? What I am doing is using a trick of power pivot to "harvest" the single value that is visible in the pivot table context by asking (in my example) what is the min value. In Ozeroth's example (Ozeroth is awesome by the way) he asked what is the max value. But if there is only one value, the answer is the same.

So don't think about max and min, but instead think of this as using max and min to "harvest" or "extract" the single value from the current filter context.

i hope this makes some sense. This is half a day in my live training classes, and several chapters in my book.
 
Last edited:
Upvote 0
Yes I realise I was light on explanation. I'm actually glad you asked. - that shows genuine curiosity about "why".

This is actually a trick. You need to forget about max and min, and start to think about filter context.


Here is my formula.

Code:
Total Sales Current + 2 Weeks := 
     Calculate(sum(Data[Value]),
           filter (All(Calendar),
                calendar[ID] >= min(Calendar[ID]) && 
                Calendar[ID] <= min(Calendar[ID])+2
          )
     )

so starting with the filter portion.


First of all, ALL(calendar) removes all filters from the current filter context. What ever filters are in the pivot table on the calendar - forget them - they are gone.

Then the next 2 lines reapply some new filters. Min(calendar[ID]) "harvests" the minimum vaule from the current filter context - in other words it looks at the pivot table and checks what filters are currently applied. In effect it "looks" at your pivot table and works out the minimum value of calendar ID for each cell in the pivot table. Now in the pivot table you built, each row has only 1 week. Each week has only one ID, right? So what is the id for (say) 201501? Well, it is a number. Let's say 7. So what is the min of 7? What is the max of 7? What is the sum of 7? What is the average of 7? The answer is always the same. The answer is 7? What I am doing is using a trick of power pivot to "harvest" the single value that is visible in the pivot table context by asking (in my example) what is the min value. In Ozeroth's example (Ozeroth is awesome by the way) he asked what is the max value. But if there is only one value, the answer is the same.

So don't think about max and min, but instead think of this as using max and min to "harvest" or "extract" the single value from the current filter context.

i hope this makes some sense. This is half a day in my live training classes, and several chapters in my book.

Thanks Matt for your explanation. After i read your formula again and again, I think i have understood what the formula does.
 
Upvote 0
There is no need to use "Reply with Quote" unless you are pointing to some specific part of a post - otherwise it is just clutter and can make the thread hard to follow
 
Upvote 0
I am not able to see pics from here, but What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Thu 17 Mar 2016) is actually 42446

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So having said that, all you need to do is structure a SUMIFS() as follows...

=SUMIFS(sum-range, date-range, ">="&today()-WEEKDAY(today(),3), date-range, "<="&(today()+7)-(WEEKDAY(today()+7,3))

This can be shortened if you put =TODAY() in it's own cell (say, A1)...
=SUMIFS(sum-range, date-range, ">="&$A$1-WEEKDAY($A$1,3), date-range, "<="&($A$1+7)-(WEEKDAY($A$1+7,3))
 
Upvote 0
Thanks Matt for your explanation. After i read your formula again and again, I think i have understood what the formula does.

Yes, it is a bit hard to get your head around. Don't worry, you are not alone - most people find it hard to understand to start with. The most important thing you need to learn is about filter context and filter propagation. These are covered in detail in my book (Chapter 5 Learn to Write DAX - Excelerator BI)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top