A quick post today that came from one of the readers on the ask a question page:
Let’s say we have a dates table and fact table linked by a field called DatesID. The dates table has one field called DatesID and the fact table has two fields (DatesID and Quantity) as follows:
We have a measure for CurrentQuantity:=SUM(fact[Quantity]). When we select 5/3/14 on a slicer for dates[DateID], we get 3 from Pivot1. Everything is fine at this point.
Where I run into trouble is with Pivot2 where I want to list all dates within the last 30 days of the slicer date along with the corresponding quantity.
So for example: By selecting 5/3/14 on the date slicer, we want to produce the following on Pivot2:
Similarly, when we select 5/2/14 on the dates slicer, we would get 2 from our CurrentQuantity measure and 5/3/14 would drop from pivot2.
So I created two tables that show the above case:
I then loaded them into the model and created a relationship:
Now I created two pivot tables with a slicer of dates in the date table (Table2), the first questions is solved automatically by creating a simple SUM(Table1[Sales]):
Now for the second, I want to see the Sum of Sales for 3 days before and on the current day. This is where some DAX magic comes into play:
=CALCULATE([Sum of Sales],
,Table2[Date] <= MAX(Table2[Date]) && Table2[Date] > MAX(Table2[Date])-3
In this calculation we are to calculate the [Sum of Sales] where we FILTER the results over ALL rows of Table2 (instead of using the date filters on the PivotTable from the slicer), then we return only the [Sum of Sales] for those rows on Table2 where the Date values of Table2 is smaller or equal to MAX Table2[Date] for the current selection (set by the slicer) and Table2[Date] is greater than the MAX of Table2[Date] minus 3 days ago.