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

By | May 26, 2014

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:

image

I then loaded them into the model and created a relationship:

image

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

image 

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:

image

  • 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

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