Managing a SCD2 dimension related to a fact table

[Anonymous]

Hi,

I've a SQL Server data mart having a SCD2 dimension (e.g. a DIM_Products dimension), a fact table (e.g. a Fact_Sales fact table) and a Calendar dimension.

The SCD2 dimension has a surrogate key (as a primary key), and effective start date and end date. The fact table has a registration date for each new record inserted and the SCD2 dimensione surrogate key. The Calendar dimension has a date for each day in a continuous manner.

I'm implementing a PBI model reading from this SQL Server data mart.

In the PBI model, I can relate the SCD2 to the fact table.

I need to select a date for the corresponding calendar filter in order to filter the SCD2 dimension rows having the filtered calendar date comprises between the dimension start date and end date. The SCD2 dimension filter propagates his effects to the fact table.

E.g., for a specific calendat date I want to filter the DIM_Products dimension to select the valid products respect to the calendar date (dimension start date <= calendar date <= dimension end date).

In order to solve this issue, I'm thinking to write a DAX expression but I'd like to find a better solution from the point of end-user view.

Any suggests to me, please? Thanks 

[dax]

Hi pmscorca,

If possible, could you please inform me more detailed information (such  as your sample data and your expected output)? Then I will help you more correctly. You could use simple data to replace your real data, then upload the pbix or simple data sample.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I've a SCD2 dimension, e.g. with these data:

Cod_Dim  Description  Start_Date     End_Date

D002        aaaa             01-01-2019   12-02-2019

D002        bbbb            13-02-2019   24-03-2019

D002        cccc              25-03-2019   30-09-2019

and I've a Calendar dimension with a date (01-01-2019, 02-01-2019, 03-01-2019, and so on).

I want creating a new SCD2 dimension by filtering the rows having the start date <= the selected calendar date and the end date >= the selected calendar date.

E.g. if I select the calendar date equals to 18-03-2019, the new SCD2 dimension has to return:

Cod_Dim  Description  Start_Date     End_Date

D002        aaaa             01-01-2019   12-02-2019

D002        bbbb            13-02-2019   24-03-2019

I'm trying this DAX formula, but unsuccessfully:

CALCULATETABLE(mySCD2;FILTER(mySCD2;mySCD2[Start_Date]<=SELECTEDVALUE(Calendar[Date]) ) )

I can see an empty table also if I select a date in the calendar dimension.

If I use:

CALCULATETABLE(mySCD2;FILTER(mySCD2;mySCD2[Start_Date]<=TODAY() ) )

I can see some rows for the new SCD2 dimension.