Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
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.
Thanks in advance,
Mark
Solved! Go to Solution.
Hi Mark,
It is the context that makes the result right or wrong. When you have a context such as dates. You could try this formula to have a test.
Last 5 days 1 = CALCULATE ( SUM ( Sales[Qty Sold] ), DATESINPERIOD ( 'Sales'[Date], MIN ( Sales[Date] ), -5, DAY ) )
If you want to use your formula in a card visualization, you need to make a little change. It's still the context that makes the tricks. The card visualization takes the whole dataset as context.
Last 5 days 2 = VAR L5D = LASTDATE ( Sales[Date] ) - 5 RETURN CALCULATE ( SUM ( Sales[Qty Sold] ), FILTER ( ALL ( Sales ), Sales[Date] > L5D ) )
Best Regards!
Dale
Hi Mark,
It is the context that makes the result right or wrong. When you have a context such as dates. You could try this formula to have a test.
Last 5 days 1 = CALCULATE ( SUM ( Sales[Qty Sold] ), DATESINPERIOD ( 'Sales'[Date], MIN ( Sales[Date] ), -5, DAY ) )
If you want to use your formula in a card visualization, you need to make a little change. It's still the context that makes the tricks. The card visualization takes the whole dataset as context.
Last 5 days 2 = VAR L5D = LASTDATE ( Sales[Date] ) - 5 RETURN CALCULATE ( SUM ( Sales[Qty Sold] ), FILTER ( ALL ( Sales ), Sales[Date] > L5D ) )
Best Regards!
Dale
Hi Mark,
Have you tried something like this:
Sales Last 5 days = Calculate ( Sum ( Sales[Qty Sold]) , DateDiff ( Sales[Date] , Now() , DAY ) < 5 )
Alternately you could create a calculated column as follows and then add this in as a visual level filter:
Is Last 5 days = If ( DateDiff ( Sales[Date] , Now() , DAY ) < 5 , "Yes" , "No" )
Thanks,
J
Thanks, I couldn't get your Sales Last 5 days measure to produce the result - it was doing the same as my measure and seemingly not recognising the date.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |