Forum Discussion

BradleyA's avatar
BradleyA
Regular Visitor
9 years ago
Solved

Working with Timespan Data

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". ...
Need Help
Tutorial Requests
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    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]
            )
    )
)

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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]
            )
    )
)