Question

You have a Sales fact table with two date columns: OrderDate and ShipDate. Both need to filter the same Date dimension table. You create an active relationship from Date[Date] to Sales[OrderDate] and an inactive relationship from Date[Date] to Sales[ShipDate]. You need a measure named 'Sales Shipped' that totals SalesAmount based on the ShipDate instead of the OrderDate. Which DAX approach should you use?

Accepted Answer

Sales Shipped = CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Date[Date], Sales[ShipDate]))

Answer

Sales Shipped = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[ShipDate] = Date[Date]))

Answer

Sales Shipped = SUMX(RELATEDTABLE(Date), SUM(Sales[SalesAmount]))

Answer

Sales Shipped = CALCULATE(SUM(Sales[SalesAmount]), CROSSFILTER(Date[Date], Sales[ShipDate], Both))