How to use a datekey table to filter a fact table on two dates

[NigelS]

Hi,

I have read several posts quite similar to what I am trying to do but can't find a solution.

I have a table called Records which has a Start Date and an End Date for each record.

Record #	Start DateKey	End DateKey
1	20171201	20180102
2	20180101	20180102
3	20180102	20180110

I want to use a slicer based on a DateKey table to select and display those records where the StartDate is after the start of the period and the EndDate is before the end of the period.

I can count the number of these records with a measure that looks like this:

No of open&closed = CALCULATE(COUNT(Records[End DateKey]),FILTER(Records,
                  AND (MIN(DateKey[DateKey])<=Records[End DateKey],
           MAX(DateKey[DateKey])>=Records[End DateKey])))

where I already have a relationship between DateKey[DateKey] and Records[Start DateKey].

But how do I display the records resulting from this kind of filter?

Thanks,

Nigel 

[v-piga-msft · Accepted solution]

Hi @NigelS,

1. You don't need to create the relationship between the two tables.

2. Then you could create a measure like below.

Measure =
VAR mindate =
    MIN ( 'Calendar'[Date] )
VAR maxdate =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        MIN ( 'Records'[Start DateKey] ) >= mindate
            && MAX ( 'Records'[End DateKey] ) <= maxdate,
        1,
        0
    )

3. Create the Between Slice with the Calendar [Date].

4. Add the measure to visual level filters in the Filter pane like the picture below. 

In addition, you could have a reference of my pbix file.

Hope it can help you!

Best Regards,

Cherry

[NigelS]

Hi @v-piga-msft,

Many thanks for your solution which has worked out perfectly for me. The reference file was very helpful. I made one small modification to remove the "All" in the measure in the fact table so as I could have other slicers applied in the same visual.

Regards,

Nigel

[NigelS]

Hi Cherry,

Thanks for your reply and making the modification to the measure so that it doesn't rely on the relationship. That all makes sense, but it doesn't address the key problem I have which is that I need to see the resulting records in a table visualisation. 

The measure shows me the count, but how can I actually see the filtered records?

Thanks,

Nigel

[NigelS]

Hi again Cherry,

Please ignore my last message. I hadn't fully understood your example. Now I am looking again more closely and I see how you display the records also. I need to test it further with the real report now but this looks very promising thanks.

Regards,

Nigel