Forum Discussion

NewPBIe's avatar
NewPBIe
Icon for Helper II rankHelper II
3 years ago
Solved

New, active and closed Contracts

Hello everybody. I've got a Date Table (Calender) and a Table with IDs, Start date and End date for some Contracts (Data).   I need to answer three different questions pro each Period (fiscal year ...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Active contracts =
VAR ReferenceDate =
    MAX ( 'Calendar'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Start date] <= ReferenceDate
            && (
                ISBLANK ( 'Table'[End date] )
                    || 'Table'[End date] > ReferenceDate
            )
    )
RETURN
    Result
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Try

Active contracts =
VAR ReferenceDate =
    MAX ( 'Calendar'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Start date] <= ReferenceDate
            && (
                ISBLANK ( 'Table'[End date] )
                    || 'Table'[End date] > ReferenceDate
            )
    )
RETURN
    Result