Forum Discussion
Filter not filtering anything?
I am trying to calculate the last 12 months of sales (not rocket science...) but my formula does not give me the expected result
I have a table 'Net Sales' related to a 'Dates' table
I tried to
1) select the last date within the current selection
2) calculate 365 days before (ignoring leap years, etc)
3) filter my 'Net Sales' using the two measures above
Somehow I get the feeling that the first 2 measures are not evaluated BEFORE being used in the 3rd measure (or something along those lines)
LastWeekOfNetSales:=CALCULATE(MAX('Net Sales'[Net Sales Date]))
51WeeksBefore:=[LastWeekOfNetSales] -365
12MRNetSales :=
CALCULATE (
SUM ( 'Net Sales'[Net Sales] ),
FILTER (
ALL ( dates ),
Dates[Date] <= [LastWeekOfNetSales]
&& Dates[Date] >= [51WeeksBefore]
)
)I have a relation between 'Net Sales'[Net Sales Date] and 'Dates'[Date]
Obviously, I am doing something wrong... :smileyfrustrated:but what?
Any idea?
Thanks
Hi Anonymous
The problem you're running into is due to context transition.
When your first two measures are evaluated in the row context generated by FILTER (i.e. in each row of Dates), due to the implicit CALCULATE that applies to any measure, context transition occurs and the row context is turned into filter context for the purpose of those measures.
The result is that the FILTER condition is TRUE for every row of Dates, because LastWeekOfNetSales and 51WeeksBefore are evaluated in the context of a single date rather than the outer filter context.
Either of these measures should be closer to what you intended, but check the date ranges are exactly what you want (the second measure filters Dates to a range containing 365 dates):
12MRNetSales := CALCULATE ( SUM ( 'Net Sales'[Net Sales] ), DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -1, YEAR ) )12MRNetSales := CALCULATE ( SUM ( 'Net Sales'[Net Sales] ), FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) && Dates[Date] >= MAX ( Dates[Date] ) - 364 ) )
5 Replies
You can write two measures to achieve desired results:
Last Date when sales occured:= CALCULATE(DATEADD(
LASTNONBLANK('Net Sales[Net Sales Date]
,SUM('Net Sales'[Net Sales]))
,-12
,MONTH)
, ALL(Net Sales))Second mesure would be
Last 12 Month Sales:= IF(MAX(Dates[Date]) >= [Last Date when sales occured],SUM('Net Sales'[Net Sales]))
Hi Anonymous
The problem you're running into is due to context transition.
When your first two measures are evaluated in the row context generated by FILTER (i.e. in each row of Dates), due to the implicit CALCULATE that applies to any measure, context transition occurs and the row context is turned into filter context for the purpose of those measures.
The result is that the FILTER condition is TRUE for every row of Dates, because LastWeekOfNetSales and 51WeeksBefore are evaluated in the context of a single date rather than the outer filter context.
Either of these measures should be closer to what you intended, but check the date ranges are exactly what you want (the second measure filters Dates to a range containing 365 dates):
12MRNetSales := CALCULATE ( SUM ( 'Net Sales'[Net Sales] ), DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -1, YEAR ) )12MRNetSales := CALCULATE ( SUM ( 'Net Sales'[Net Sales] ), FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) && Dates[Date] >= MAX ( Dates[Date] ) - 364 ) )
