Comparing two sets sets of SCD-2 table based on "from date" and "to date"

[Anonymous]

Hi,

I have a SCD-2 table that has basically the following structure:

The user can select two independent snapshot dates "From (A)" and "To (B)" and the Power BI report should display the values of from (A) to (B) in a table like below:

The SCD-2 table has hunbreds of thousands of records and there are many other measures that need to be calculated based on the selected keys (records). I am looking for the most efficient way of doing this.

Thanks,

[Anonymous]

With two tables how do I get to the single report table described above?

[Anonymous]

Hi @Anonymous,

After I further test with your scenario I found my suggestion can filter the match records but these results can't be merged into one row. They will keep corresponding records part, other parts will display blank.
For example:
row 1: start part, blank
row 2: blank, end part
row 3: start part, blank
row 4: blank, end part

Regards,
Xiaoxin Sheng

[Anonymous · Accepted solution]

HI @Anonymous,

You can't use only one table to achieve your requirement.

In my opinion, I'd like to suggest you duplicate the row table and use one as the start and another one as the end to create the table visual to show the results. 

For the date slicer, I think you need to create an unconnected date table as the source of the slicer and use the min date on one table, the max on another table for filter operations.

Regards,

Xiaoxin Sheng