# Show the sum of sales for the last 3 days based on date selection

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:

Date, Quantity
5/1/14, 1
5/2/14, 2
5/3/14, 3

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:

Date, Quantity
5/1/14, 1
5/2/14, 2
5/3/14, 3

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],

FILTER(ALL(Table2)

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

Result:

## 2 Replies to “Show the sum of sales for the last 3 days based on date selection”

1. So very simple and straight forward, yet it solves an issue I’ve been using VBA to workaround for a couple of years now. Thanks so much…this is great!

Best Regards,

Tak

2. Sure gets around the calculation hog EARLIER function. Thanks!

This site uses Akismet to reduce spam. Learn how your comment data is processed.