Forum Discussion
Reference a previous month using variables in DAX using slicers
Community Championok just that your code looks a bit stranger to me i am not sure what you doing
Last Week Targets = VAR myMax = MAX('Calendar$'[Weeknum]) RETURN CALCULATE(SUM(internetTargets[Impressions]),ALLEXCEPT(internetTargets,internetTargets[weeknum],joinTable[Website],'Calendar$'[Year],'Calendar$'[Month Name]),internetTargets[weeknum]=myMax-1)
firstly it looks like you using 2 tables, is the week number in the calendar table?
secondly your relationships between your date table and internettargets is there one?
essentially though you need to be doing a filter something to this effect
Last Week Targets = VAR myMax = MAX('Calendar$'[Weeknum])
RETURN CALCULATE(SUM(internetTargets[Impressions]), filter('Calendar$', 'Calendar$'[Year]' = Year(now()) &
mymax='calendar'[weeknum]-1)
am sure this isn't right but need to know more about how your data is structured
Yep
three tables
one contains date
two contains internet page impressions targets
three contains ga page impressions
There is a week num column in each table and 2/3 joins the calendar table via the date column
Tried what you suggest but it throws up the error
MdxScript(Model) (9, 73) Calculation error in measure 'GA'[Last Week Targets]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.All values are whole numbers.....
After some more testing it seems it does not like the YEAR function