Trouble with SUM - Multiple Filters and ORs Applied

[julesdude]

Hi there,

Here is a modified extract from a table in my data model....bear in mind the table has a number of different Asset References, but I've listed a group of AAA here: 

Asset Reference	Leased  Area	Lease.Lease  Status	Lease.Commencement  Date	Lease.Expiration   Date	Lease.Termination Date
AAA	12542	Holding Over	29-Sep-90	28-Sep-15
AAA	6809	Active	23-Feb-22	31-Dec-99
AAA	6809	Active	22-Jul-16	21-Jul-26
AAA	6992	Active	29-Sep-14	28-Sep-24
AAA		Active	15-Sep-22	14-Sep-37
AAA	12010	Active	19-Mar-19	18-Sep-19
AAA	16575	Active	03-Feb-12	02-Feb-27
AAA	7000	Active	14-May-22	13-May-27
AAA	7000	Active	15-Aug-13	10-Nov-99
AAA	12249	Active	09-Apr-13	08-Apr-23
AAA	1241	Active	14-Aug-19	13-Aug-29
AAA		Active	01-Apr-19	31-Dec-99
AAA	0	Holding Over	29-Apr-15	28-Apr-21

I am trying to SUM the Leased Area column.  But I need to do so applying the following conditions:

1. The [Lease.Commencement Date] must be <= my measure [as of date]         (as of date is a date picker in my report)

2. Either the [Lease.Expiration Date] must be >= [as of date] OR the [Lease.Lease Status] must either be 'Holding Over' OR 'Month-to-Month'

3. The [Lease.Termination Date] must be blank. However, if there is a date there, it must be >= [as of date]

Based on this logic with an [as of date] = 31/12/2022, I'd want the following green items summed for this asset:

My current DAX brings up the error - 'Argument 7 in CALCULATE function is required'. I do not know what is going wrong and not even sure my method is the best approach. 
Current code:

Total Leased Area (Current) = 
VAR _asofdate = [As Of Date]
VAR _assetref = MAX(Building[Asset Reference])
VAR _TotalLeasedArea = 
CALCULATE (
    SUM(Lease_Unit[Leased Area] ),
    
    Lease_Unit[Asset Reference] = _assetref,

    Lease_Unit[Lease.Commencement Date] <= _asofdate,

    FILTER(Lease_Unit, 
        Lease_Unit[Lease.Expiration Date] >= _asofdate &&
            OR(
                Lease_Unit[Lease.Lease Status] = "Holding Over", 
                Lease_Unit[Lease.Lease Status] = "Month-to-Month"
            )
        ),

    FILTER(Lease_Unit,
        OR(
            Lease_Unit[Lease.Termination Date] >= _asofdate,
            ISBLANK( Lease_Unit[Lease.Termination Date] )
        ) 
    ),

        FILTER (Unit,  
            OR (
                Unit[Unit Start Date] <= _asofdate,
                ISBLANK(Unit[Unit Start Date] )
            )
        && OR ( 
            Unit[Unit End Date] >= _asofdate,
            ISBLANK(Unit[Unit End Date])
        )
    ),
)
RETURN
_TotalLeasedArea

Any help appreciated with more efficient code without error.

[wdx223_Daniel]

The comma in the fourth line from the bottom is redundant

[julesdude]

Thanks @wdx223_Daniel  - that was it!

[smpa01 · Accepted solution]

@julesdude  the table has to pass all 3 tests. So you can chain the test rather than find the instersection of all the conditions at once

Measure = 
VAR asOfDate =
    DATE ( 2022, 12, 31 )
VAR filtTblOne =
    FILTER ( tbl, tbl[Lease.Commencement  Date] <= asOfDate )
VAR filtTblTwo =
    FILTER (
        filtTblOne,
        tbl[Lease.Expiration   Date] >= asOfDate
            || ( tbl[Lease.Lease  Status] = "Holding Over"
            || tbl[Lease.Lease  Status] = "Month-to-Month" )
    )
VAR filtTblThree =
    FILTER (
        filtTblTwo,
        tbl[Lease.Termination Date] = BLANK ()
            || tbl[Lease.Termination Date] >= asOfDate
    )
RETURN
    CALCULATE ( SUM ( tbl[Leased  Area] ), filtTblThree )

Internally DAX generates this forEach Asset Reference dropped into the viz