dynamically Calculating a foreign key for fact table using Dax

[wilson_smyth]

I have a Period dimension table that i wish to link with my fact table. 
There is no foreign key column in the fact table for Period. I can add it during the load, but would like to see about doing it dynamically using dax and could use some help.

I have some sample data below. Dimuser links to Fact on UserID.
DimPeriod will  link to Fact on PeriodID, but PeriodID in fact table needs to be calculated.

Its not as simple as saying where transactionDate between Period start & End Date. this is because periods can have same start and end dates, but differ based on when the user joined. i.e. 

If a user joins in 2010 and creates a transaction on 31/12/2011, it will be in period 4, but if a user joined in 2011 and had a transaction on the same day, it would be in period 6.

I have manually calculated PeriodID in the fact table below but am struggling to do this in DAX.

I hope that makes sense. If i can clarify anymore please let me know.

DimUser
userid	yearStarted
1	2010
2	2010
3	2011
4	2011

Fact
FactID	UserID	TransactionDate	PeriodID	Measure1
1	1	28/03/2011	3
2	1	30/03/2011	3	8
3	2	1/8/2010	2	4
4	3	15/10/2011	6	2
5	4	30/12/2012	8	8
6	1	10/5/2011	3	7
7	2	28/02/2011	3	2
8	3	30/05/2012	7	9
9	1	31/10/2010	4	7
10	4	20/01/2011	1	6

DimPeriod
PeriodID	PeriodYear	YearPart	StartDate	EndDate
1	2010	1	1/1/2010	30/06/2010
2	2010	2	1/7/2010	31/12/2010
3	2010	3	1/1/2011	30/06/2011
4	2010	4	1/7/2011	31/12/2011
5	2011	1	1/1/2011	30/06/2011
6	2011	2	1/7/2011	31/12/2011
7	2011	3	1/1/2012	30/06/2012
8	2011	4	1/7/2012	31/12/2012

[v-chuncz-msft · Accepted solution]

@wilson_smyth,

You may use DAX below to add a calculated column.

Column =
MAXX (
    FILTER (
        DimPeriod,
        DimPeriod[PeriodYear] = RELATED ( DimUser[yearStarted] )
            && DimPeriod[StartDate] <= 'Fact'[TransactionDate]
            && DimPeriod[EndDate] >= 'Fact'[TransactionDate]
    ),
    DimPeriod[PeriodID]
)

[stretcharm]

Why do you need to calculate in Dax?

Given the link is not simple it would make more sense to have on the fact. Remember the data is compressed so if you not got many values it will not table up much space even with a large number of rows.