DAX Measure filtering on a column in a RELATED table

Question

I am trying to create a DAX measure that returns the last 4 weeks Sales.

My powerpivot has a separate calendar table which is linked to the sales table by the date, the calendar in turn has a column that indicates the rolling period that the date in question belongs to. My aim is to sum all sales where the saledate has a 4 in the rolling period column for the corresponding date.

My attempt was:

=CALCULATE(SUM('Sales Data'[Sales Ex GST]),RELATED(Calendar[Rolling Weeks])="4")

Which doesn't work! Any help most appreciated!

Jacob

Answer 1

Try this:

=SUMX(FILTER('Sales Data',RELATED(Calendar[Rolling Weeks])=4),'Sales Data'[Sales Ex GST])

 

Note: the 4 should only have double quotes around it if [Rolling Weeks] is a text field.

Answer 2

Awesome! Did the trick. Thanks.

Answer 3

The following is probably more efficient:

=CALCULATE(SUM('Sales Data'[Sales Ex GST]),FILTER(Calendar,Calendar[Rolling Weeks]=4))