Sum of Next 3 Weeks

[pociners]

Hi Everyone,

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

This is the result that i want

Can you help me how to calculate that in powerpivot?

thanks and best regards

 

[Matt Allington]

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

 

[pociners]

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

[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

[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

 

[Matt Allington]

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.

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.

 

[pociners]

ADVERTISEMENT

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.

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.

 

[FDibbins]

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

 

[FDibbins]

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

 

[Matt Allington]

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)