I am trying to create a DAX formula that will calculate my total goods sold in the last n days.
The formula below gets me the correct result - using the date function with a hard coded date: Sales Last 5 days = CALCULATE(SUM(Sales[Qty Sold]),FILTER(Sales,Sales[Date]>=date(2017,3,11)))
The problem starts when I try and replace the date function above with a measure. Something like: Last5Days = lastdate(Sales[Date])-5). This measure returns the correct date and displays the date correctly in a card visualisation.
When I try the measure as part of a similar expression, the result is not correct - it returns the total qty sold across the whole dataset: Last 5 days = CALCULATE(SUM(Sales[Qty Sold]),FILTER(Sales,Sales[Date]>=lastdate(Sales[Date])-5))
Is anyone able to help me both why the measure doesn't work and what I need to change to make it work.