Hi guys, I know this is probably very simple but I just can’t get it working, here is the situation:
My fact table data is at the grain of Date > Store > Department. Each fact table row has a DateID which keys to the date dimension dimension_Date. The sale amount in this case is called “Actual”.
Dimension_Date has attributes for FiscalWeek, DayOfWeek, LastYearDateID. (Amongst many others).
I am wanting to calculate the value for “same day last week” and “same day last year”. It should be easy as these attributes are already on the date table! (I would have thought).
Here is what I have tried:
Actual LW = VAR CurrentWeek = SELECTEDVALUE(Dimension_Date[FiscalWeek]) VAR CurrentDay = SELECTEDVALUE(Dimension_Date[DayOfWeek]) RETURN CALCULATE([Actual], FILTER(ALL(Dimension_Date), Dimension_Date[DayOfWeek] = CurrentDay && Dimension_Date[FiscalWeek] = CurrentWeek - 1))
This just returns all blanks though!
Any help gratefully received.