Working with Timespan Data

[BradleyA]

I am working with data that's has a start date and end date (or null if the engagement has not ended). Between the start and end dates, the engagement is "open" and outside of them, it is "closed".

I want to include a slicer that will allow me to select given month(s) of the year and have visualizations be updated with info for all "open" engagements.

Example:
Sample record 1 has
Open date = 1/1/2017
Close date = 7/31/2017

Sample record 2 has
Open date = 5/16/2017
Close date = 8/15/2017

If I filter on "March 2017", sample record 1 will be included.

If I filter on "June 2017", sample records 1&2 will be included.

If I filter on "August 2017", sample record 2 will be included.

If I filter on "March 2016", neither record will be included.

I somehow need to generate a list of "open months" based on start and end dates. I know how to do this in Access using a list of months in a separate table and a "no join" (not sure what it is called when I don't join on any fields).

Any advice for how to do this in Power BI is appreciated? Thank you!

[Eric_Zhang · Accepted solution]

@BradleyA

You can try measures as below. See more details in the attached pbix file.

IsIncluded = 
IF (
    (
        MIN ( 'calendar'[Date] ) >= MAX ( yourTable[Open date] )
            && MIN ( 'calendar'[Date] ) <= MAX ( yourTable[Close date] )
    )
        || (
            MAX ( 'calendar'[Date] ) >= MAX ( yourTable[Open date] )
                && MAX ( 'calendar'[Date] ) <= MAX ( yourTable[Close date] )
        ),
    1,
    BLANK()
)

includedRecords =
CALCULATE (
    COUNTROWS ( yourTable ),
    FILTER (
        ALLSELECTED ( yourTable ),
        (
            MIN ( 'calendar'[Date] ) >= yourTable[Open date]
                && MIN ( 'calendar'[Date] ) <= yourTable[Close date]
        )
            || (
                MAX ( 'calendar'[Date] ) >= yourTable[Open date]
                    && MAX ( 'calendar'[Date] ) <= yourTable[Close date]
            )
    )
)