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.