Week over Week difference with slicer to change the "current date"

[OldN00b]

I am trying to calculate the 1 week change in an index level (Yield Bps) based on a selected date within a slicer.

In my data, "Date" in Data table represents the current date in the slicer, calendar DatePreviousWeek in date table represents the 1 week from selected date. How can i show the change from these two fields for Yield (bps)?

Below is an example of my data. The Field "Calendar DatePreviousWeek" comes from the Date table. The rest of the fields are within the data table. The data and date table share a one to one relationship with each other.

Slicer = Date Field in Data Table

calculation is simply (Yield where date = selected date in slicer) - (yield where date = Calendar DatePreviousWeek)

Index Identifier	DATE	Calendar DatePreviousWeek	Yield (Bps)
BAMLC0A0CM	1/3/2022 0:00	12/27/2021 0:00	97
BAMLC0A0CM	1/4/2022 0:00	12/28/2021 0:00	96
BAMLC0A0CM	1/5/2022 0:00	12/29/2021 0:00	95
BAMLC0A0CM	1/6/2022 0:00	12/30/2021 0:00	95
BAMLC0A0CM	1/7/2022 0:00	12/31/2021 0:00	95
BAMLC0A0CM	1/10/2022 0:00	1/3/2022 0:00	96
BAMLC0A0CM	1/11/2022 0:00	1/4/2022 0:00	96
BAMLC0A0CM	1/12/2022 0:00	1/5/2022 0:00	96
BAMLC0A0CM	1/13/2022 0:00	1/6/2022 0:00	97
BAMLC0A0CM	1/14/2022 0:00	1/7/2022 0:00	97
BAMLC0A0CM	1/17/2022 0:00	1/10/2022 0:00	97
BAMLC0A0CM	1/18/2022 0:00	1/11/2022 0:00	98
BAMLC0A0CM	1/19/2022 0:00	1/12/2022 0:00	99
BAMLC0A0CM	1/20/2022 0:00	1/13/2022 0:00	100
BAMLC0A0CM	1/21/2022 0:00	1/14/2022 0:00	103
BAMLC0A0CM	1/24/2022 0:00	1/17/2022 0:00	105
BAMLC0A0CM	1/25/2022 0:00	1/18/2022 0:00	105
BAMLC0A0CM	1/26/2022 0:00	1/19/2022 0:00	103
BAMLC0A0CM	1/27/2022 0:00	1/20/2022 0:00	107
BAMLC0A0CM	1/28/2022 0:00	1/21/2022 0:00	109
BAMLC0A0CM	1/31/2022 0:00	1/24/2022 0:00	110
BAMLC0A0CM	2/1/2022 0:00	1/25/2022 0:00	108

[DimaMD]

@OldN00b Hi, Can you provide your expected result?

[OldN00b]

Current_Bps is a measure that represents the Yield (Bps) column for a selected date in a slicer (in this case 12/14/2022).  1_week_ago_bps is a measure that represents the Yield (Bps) column using the 1_week_ago_date measure as a filtering criteria. 

My expected results are difference between current_bps-1_week_ago_bps measures. The issue is that the 1_week_ago_bps measure is returning null and i don't know why / how to fix it.

Below are the formulas to calculate each measure:

Bps Chg. 1Week =

var num = CALCULATE(max('FRED Data'[Yield (Bps)]),FILTER(all('FRED Data'[Date]),'FRED Data'[Date] = [current_date]))

var den = CALCULATE(max('FRED Data'[Yield (Bps)]),FILTER('FRED Data','FRED Data'[Date] = [1_week_ago_date]))

var calc = num - den return calc

[OldN00b]