Forum Discussion
Clara
Advocate II
Advocate IIUsing both primary (active) and secondary (inactive) relationship in a measure?
Hi all! Here's my problem: I have two tables, and each of them have a Date and a Unit column. I've created a date table and successfully set up a relationship between the two tables through their...
I have! I might have figured it out.
I decided not to use SAMEPERIODLASTYEAR (though I'm not sure it has any part in making it work) and instead ended up with this measure:
SalesTotal_LY =
VAR LastYr = DATEADD(Calendar[MonthYear],-1,YEAR) RETURN CALCULATE(
SUM(Table1[Sales]),
USERELATIONSHIP(Table1[Units],Table2[Units]),
Table1[MonthYear]=LastYr,
FILTER(ALLEXCEPT(Table1,Table1[MonthYear]),
Table1[Units]=[RelUnit]),
Table1[ItemId]="001")[RelUnit] looks like this (I'm not even sure I need it, but haven't tried taking it out yet:
RelUnit = CALCULATE(MIN(Table1[Units]),USERELATIONSHIP(Table1[Units],Table2[Units]))
ClaraAdvocate II
Advocate III have! I might have figured it out.
I decided not to use SAMEPERIODLASTYEAR (though I'm not sure it has any part in making it work) and instead ended up with this measure:
SalesTotal_LY =
VAR LastYr = DATEADD(Calendar[MonthYear],-1,YEAR) RETURN CALCULATE(
SUM(Table1[Sales]),
USERELATIONSHIP(Table1[Units],Table2[Units]),
Table1[MonthYear]=LastYr,
FILTER(ALLEXCEPT(Table1,Table1[MonthYear]),
Table1[Units]=[RelUnit]),
Table1[ItemId]="001")
[RelUnit] looks like this (I'm not even sure I need it, but haven't tried taking it out yet:
RelUnit = CALCULATE(MIN(Table1[Units]),USERELATIONSHIP(Table1[Units],Table2[Units]))
Okay, good. I think it should do the same thing but its often easier to use dateadd when you're unsure. Are you able to verify the result?