SUMX Measure only works at row level

[Anonymous]

I have a large sales model, with an unusual financial calendar, based on weeks and periods, rather than months and quarters.

My calendar table has a row for every date, and then which year/week/period etc it falls into. I've made a calculated column that spits out the date in the format year / week / day of week (e.g. 2018-W40-1 for the Monday of week 40 in FY ending 2018). I've used this to create a column for Calendar Date LY, using lookupvalue, and producing the date in the same format, but with the FY as -1.

I've then used this measure to create Actual Sales LY, which works as expected in a visual showing at date level.
Actual Sales PY = CALCULATE(sumx(Sales,[Actual Sales Value]),filter(all('Calendar'[Date]),'Calendar'[Date]=SELECTEDVALUE('Calendar'[Calendar Date LY])))

The problem arises when I want to summarize by week or period. The Actual Sales Value works for the date selected, but appears blank against the week or period on the table/matrix visual.

I think it must be to do with the SELECTEDVALUE function, which is looking for a single value, yet I want 7 or 28 values to summarize. Is there a better way to write this measure which will allow me to summarize at the different levels?

Shown at period level in table visual:

Same visual with Dates added in

Any ideas would be enormously appreciated!

[Cmcmahan · Accepted solution]

You're right in that the issue is with SELECTEDVALUE.  When there are multiple options (in this case, multiple dates within the period) it returns BLANK() or a default you set. Let's walk through your filter expression, since that's the cause of the issue:

FILTER (
    ALL ( 'Calendar'[Date] ),
    'Calendar'[Date] = SELECTEDVALUE ( 'Calendar'[Calendar Date LY] )
)

So the first thing you do is remove any filters that apply to the Calendar, and then re-apply the filter where the date is last year's date.  This is only working when you have one date in the current context.  What you actually want to be doing is to get a list of currently selected LY dates, and then filter based on those dates.  

Try updating your filter to use IN VALUES instead of = SELECTEDVALUE

FILTER (
    ALL ( 'Calendar'[Date] ),
    'Calendar'[Date] IN VALUES( 'Calendar'[Calendar Date LY] )
)