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).
[COLOR=#333333]min(Calendar[ID]) && Calendar[ID] <= min(Calendar[ID])+2[/COLOR]
[COLOR=#333333]Table1[YearWeek] <= MAX ( Table1[YearWeek][/COLOR]
Total Sales Current + 2 Weeks :=
Calculate(sum(Data[Value]),
filter (All(Calendar),
calendar[ID] >= min(Calendar[ID]) &&
Calendar[ID] <= min(Calendar[ID])+2
)
)
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.
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.