Return a Value when Column A matches Column B at any Point

[Jnwin]

Hello - Looking for assistance with desiging a measure. Essentially looking for the previous year value based on 2 IDs from 2 different tables.  If  'Fact Table'[WeekEndIDPY] = 'Date Table'[WeekendID] at any point then return that value.

I do have a year filter that I use coming from the date table, but when used I no longer will have any matches since when applied it filters out the WeekEndIDPY that I need to find the matches for:

Column 1: WeekEnddID: this is the current Week IDs
Column 2: WeekEndIDPY: This is the previous year Week IDs. 

For Example with the data below where WeekendIDPY = 1830 and WeekendID = 1830 , so the result should be 1 Unit (plus all the other ones that match in that year).

Date Table:

WeekEndDID	WeekEndDIDPY	Year
1830	1466	2020
1837	1473	2020
1844	1480	2020
1851	1487	2020
1858	1494	2020
1865	1501	2020
1872	1508	2020
1879	1515	2020
1886	1522	2020
1893	1529	2020
1900	1536	2020
1907	1543	2020
1914	1550	2020
1921	1557	2020
1928	1564	2020
1935	1571	2020
1942	1578	2020
1949	1585	2020
1956	1592	2020
1963	1599	2020
1970	1606	2020
1977	1613	2020
1984	1620	2020
1991	1627	2020
1998	1634	2020
2005	1641	2020
2012	1648	2020
2019	1655	2020
2026	1662	2020
2033	1669	2020
2040	1676	2020
2047	1683	2020
2054	1690	2020
2061	1697	2020
2068	1704	2020
2075	1711	2020
2082	1718	2020
2089	1725	2020
2096	1732	2020
2103	1739	2020
2110	1746	2020
2117	1753	2020
2124	1760	2020
2131	1767	2020
2138	1774	2020
2145	1781	2020
2152	1788	2020
2159	1795	2020
2166	1802	2020
2173	1809	2020
2180	1816	2020
2187	1823	2020
2192	1830	2020
2201	1837	2021
2208	1844	2021
2215	1851	2021
2222	1858	2021
2229	1865	2021
2236	1872	2021
2243	1879	2021
2250	1886	2021
2257	1893	2021
2264	1900	2021
2271	1907	2021
2278	1914	2021
2285	1921	2021
2292	1928	2021
2299	1935	2021
2306	1942	2021
2313	1949	2021
2320	1956	2021
2327	1963	2021
2334	1970	2021
2341	1977	2021
2348	1984	2021
2355	1991	2021
2362	1998	2021
2369	2005	2021
2376	2012	2021

Fact Table:

WeekEndDID	Consumer Units
1830	1
1837	2
1844	3
1851	4
1858	5
1865	6
1872	7
1879	8
1886	9
1893	10
1900	11
1907	12
1914	13
1921	14
1928	15
1935	16
1942	17
1949	18
1956	19
1963	20
1970	21
1977	22
1984	23
1991	24
1998	25
2005	26
2012	27
2019	28
2026	29
2033	30
2040	31
2047	32
2054	33
2061	34
2068	35
2075	36
2082	37
2089	38
2096	39
2103	40
2110	41
2117	42
2124	43
2131	44
2138	45
2145	46
2152	47
2159	48
2166	49
2173	50
2180	51
2187	52
2192	53
2201	54
2208	55
2215	56
2222	57
2229	58
2236	59
2243	60
2250	61
2257	62
2264	63
2271	64
2278	65
2285	66
2292	67
2299	68
2306	69
2313	70
2320	71
2327	72
2334	73
2341	74
2348	75
2355	76
2362	77
2369	78
2376	79

Thanks in advanced!

[Jnwin]

I got it to work by using the following formula, but it ignores the relationships with my other tables (product table). I'm assuming because I'm using calculate and filter together:

PY Units =

CALCULATE (

[Consumer Units],

FILTER (

ALL('Measures'),

' Measures'[WeekEndDID] IN VALUES ( Calendar[WeekEndDIDPY] )

)

)

[Fowmy]

@Jnwin 

You can create the following measure and add it in a Table visual and verify:

PY Units = 
CALCULATE(
    SUM(Table1[Consumer Units]),
    FILTER(
        ALL(Table1),
        Table1[WeekEndDID] = SELECTEDVALUE(Table1[WeekEndDIDPY]) &&
        Table1[Year] = SELECTEDVALUE(Table1[Year])-1
    )
)

Check your Year at data point for  Consumer Unit 53, the year should be 2021 I think
 

[Jnwin]

I forgot to mention that the consumer units is coming from a fact table, and the week end dates are within a date table, they are joined by week end date IDs. Does this change anything? I'm trying to reproduce this in my report and getting blank results. 

[Fowmy]

@Jnwin 

it should work as long as you have set up the measure correctly and your relationship is valid. Can you please share your measure for me to check.

Also share the model view of your tables

[Jnwin]

My measure is similiar as yours but I'm struggling with getting the selectedvalue of the year since it's coming from the date table:

PY Units =

CALCULATE(

[Consumer Units],

FILTER(

ALL('Measures'),

'Measures'[WeekEndDID] = SELECTEDVALUE(Calendar[WeekEndDIDPY]) &&

Table1[Year] = SELECTEDVALUE(Table1[Year])-1

)

)

I'm not sure if

SELECTEDVALUE(OfftakeWeeklyCalendar[WeekEndDIDPY] works here since I'm not actively filtering on weekendidpy, but the year itself. 

[AlexisOlson]

I think you could use LOOKUPVALUE here.

You could write a calculated column like this:

LOOKUPVALUE ( Table1[Consumer Units], Table1[WeekEndID], Table1[WeekEndIDPY] )

For a measure, it depends on what you're trying to do. If you're trying to look up the value for a single selected value, then it wouldn't be much different.

LookupMeasure =
VAR Selected_PY_ID = SELECTEDVALUE ( Table1[WeekEndIDPY] )
RETURN
    LOOKUPVALUE ( Table1[Consumer Units], Table1[WeekEndID], Selected_PY_ID )