Creating a Relationship where the Fact row Must Between Two Values

[asjones]

I have to join two tables based on a few different attributes. I know I can do part of this by using Power Query and concatenating to fields. However the challenge is one of the dimensions varies based on a time frame in the format of a year/month.

Fact table

Client	Dept	Period	Value
AB	100	201901	10
AB	100	202002	20
ZY	100	201905	30

Dimension Table

Client	Dept	Division	Period_From	Period_To
AB	100	Exec	201901	201904
AB	100	F&A	201905	202012
ZX	100	PRG	201901	202112

I can concatenate Client and Dept on both tables in Power Querty to do a Join but how do I handle if the Period column is in-between the Period_From and Period To in the Dimension table? I know how I would do this in a traditional SQL Join with a between statement but how do I create the relation in a Power BI Data Model?

Any ideas and thoughts?

thanks

Alan

[amitchandak]

@asjones 

One way is you create a new version of the table when you can all period from the between the range .

Refer how the new table is created using sheet and date for dates, you can do same for the period

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

Refer to these SCD docs

https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/

https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/

[asjones]

@amitchandak 

Thanks for the response, that is an interesting solution. It seems like there should be a better way than creating entry for ever point between tow periods (dates). In my case the default Period_to is 209912. So going from say 201000 to 209912 is a huge nubmer of entries to dynamcially create in a table and deal with....

I do aprpecite the response, would loveto hear if anyone has other alternate ideas.

thanks

Alan