locked
DAX Measure filtering on a column in a RELATED table RRS feed

  • 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

    Thursday, January 19, 2012 5:40 AM

Answers

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

    • Marked as answer by barnettjacob Monday, January 23, 2012 8:15 AM
    Thursday, January 19, 2012 9:33 PM

All replies

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

    • Marked as answer by barnettjacob Monday, January 23, 2012 8:15 AM
    Thursday, January 19, 2012 9:33 PM
  • Awesome! Did the trick. Thanks.
    Monday, January 23, 2012 8:15 AM
  • The following is probably more efficient:

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

    Monday, January 23, 2012 3:16 PM